CpS 401 Notes

SQL Injection

Introduction

SQL Injection occurs when an attacker is able to modify a SQL query at execution time by "injecting" additional code.

Demo: http://csunix.cs.bju.edu/cps401/examples/php/sqlinject.php

• BJU ID: 111111

• BJU ID: ' or 1=1 -- 
  (note trailing space)

This results in the following SQL:

SELECT * from USAGE_LOG WHERE ID = '' or 1=1 -- '

Some databases allow multiple SQL statements to be executed in a single API call. Attackers can abuse that to "piggyback" additional queries and execute any code that the database user has privilege to perform.

Examples:

• SELECT * from USAGE_LOG WHERE ID = ''; DELETE FROM USAGE_LOG WHERE ID = '111111' -- '

Preventing SQL Injection

• Avoid dynamically constructing queries by inserting user string input
  
  sql = "select * from foo where id = '" & id & "' ..."

• When possible, use query parameters, which provide rock-solid protection against SQL injection
  
  PHP:
  
  $stmt = $dbh->prepare("SELECT * from USAGE_LOG WHERE ID = ?");
  $stmt->execute(array($bjuid));
  
  Java/Spring:
  
  jt = new SimpleJdbcTemplate(dataSource);
  jt.update("update mytable set name = ? where id = ?", name, id);
  
  
  

• If query parameters are not possible, use a two-pronged defense:

  • Reject suspicious data:
    Use a regular expression or other technique to reject user input values that contain anything other than alphanumeric symbols and limited punctuation appropriate to the application

  • Escape single quotes:
    When single quotes must be allowed in user's criteria string, always escape them by doubling them
    
    Java:
    
    ql = "select * from cust where last_name = '"
    + lname.Replace("'", "''") + "' ..."
    
    This will allow the database to properly process the single quote when it is inserted into the SQL