Security - SQL Injection (SQLi) - Primary Defenses

• Use Prepared Statements (Parameterized Queries)
• Change default passwords

The use of prepared statements with variable binding (aka parameterized queries) is how all developers should first be taught how to write database queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.

Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. In the safe example below, if an attacker were to enter the userID of tom' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string tom' or '1'='1.

The MySQL Improved extension handles bound parameters.

$stmt = $db->prepare('update people set name = ? where id = ?');
$stmt->bind_param('si',$name,$id);
$stmt->execute();

or

$stmt = $dbh->prepare('update mb_users set password = ? where username = ?'); 
$dbh->execute($stmt, array('12345', 'bob')); 

ADODB provides a way to prepare, bind and execute all in the same method call.

$dbConnection = NewADOConnection($connectionString);
$sqlResult = $dbConnection->Execute(
    'SELECT user_id,first_name,last_name FROM users WHERE username=? AND password=?',
    array($_REQUEST['username'], sha1($_REQUEST['password'])
);

$stmt = odbc_prepare( $conn, 'SELECT * FROM users WHERE email = ?' );
$success = odbc_execute( $stmt, array($email) );

or:

$res = odbc_exec($conn, 'SELECT * FROM users WHERE email = ?', array($email));
$sth = $dbh->prepare('SELECT * FROM users WHERE email = :email');
$sth->execute(array(':email' => $email));

Here's the long way to do bind parameters.

$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
$stmt = $dbh->prepare('INSERT INTO REGISTRY (name, value) VALUES (:name, :value)');
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
 
// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

And a shorter way to pass things in.

$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
$stmt = $dbh->prepare('UPDATE people SET name = :new_name WHERE id = :id');
$stmt->execute( array('new_name' => $name, 'id' => $id) );

$result = pg_query_params( $dbh, 'SELECT * FROM users WHERE email = $1', array($email) );

The JDBC API has a class called PreparedStatement which allows the programmer to safely insert user-supplied data into a SQL query. The location of each input value in the query string is marked with a question mark. The various set*() methods are then used to safely perform the insertion.

String name = //user input
int age = //user input
Connection connection = DriverManager.getConnection(...);
PreparedStatement statement = connection.prepareStatement(
        "SELECT * FROM people WHERE lastName = ? AND age > ?" );
statement.setString(1, name); //lastName is a VARCHAR
statement.setInt(2, age); //age is an INT
ResultSet rs = statement.executeQuery();
while (rs.next()){
    //...
}

Hibernate uses named parameters to safely insert data into a query. A named parameter consists of a colon, followed by a unique name for the parameter.

String name = //user input
int age = //user input
Session session = //...
Query query = session.createQuery("from People where lastName = :name and age > :age");
query.setString("name", name);
query.setInteger("age", age);
Iterator people = query.iterate();

Perl's DBI, available on the CPAN, supports parameterized SQL calls. Both the do method and prepare method support parameters (“placeholders”, as they call them) for most database drivers. For example:

$sth = $dbh->prepare("SELECT * FROM users WHERE email = ?");
foreach my $email (@emails) {
    $sth->execute($email);
    $row = $sth->fetchrow_hashref;
    [...]
}

However, you can't use parameterization for identifiers (table names, column names) so you need to use DBI's quote_identifier() method for that:

# Make sure a table name we want to use is safe:
my $quoted_table_name = $dbh->quote_identifier($table_name);
 
# Assume @cols contains a list of column names you need to fetch:
my $cols = join ',', map { $dbh->quote_identifier($_) } @cols;
 
my $sth = $dbh->prepare("SELECT $cols FROM $quoted_table_name ...");

You could also avoid writing SQL by hand by using DBIx::Class, SQL::Abstract etc to generate your SQL for you programmatically.

This example uses Python DB-API with SQLite and paramstyle='qmark'.

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
 
_users = [('mother', 'red'),
          ('father', 'green'),
          ('me', 'blue')]
c.executemany('INSERT INTO users VALUES (?,?)', _users)
 
params = ('sister', 'yellow')
c.execute('SELECT * FROM users WHERE username=? AND room=?', params)
c.fetchone()

This example uses C# and ADO.NET.

USING (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";
 
    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);
 
    USING (SqlDataReader dataReader = command.ExecuteReader())
    {
        // ...
    }
}

Use sqlite3_prepare() to create a statement object.

  sqlite3_stmt *stmt;
 
  IF ( sqlite3_prepare(
         db, 
         "insert into foo values (?,?)",  // stmt
        -1, // IF than zero, THEN stmt IS READ up TO the FIRST nul terminator
        &stmt,
         0  // Pointer TO unused portion OF stmt
       )
       != SQLITE_OK) {
    printf("\nCould not prepare statement.");
    RETURN 1;
  }
 
  printf("\nThe statement has %d wildcards\n", sqlite3_bind_parameter_count(stmt));
 
  IF (sqlite3_bind_double(
        stmt,
        1,  // INDEX OF wildcard
        4.2
        )
      != SQLITE_OK) {
    printf("\nCould not bind double.\n");
    RETURN 1;
  }
 
  IF (sqlite3_bind_int(
        stmt,
        2,  // INDEX OF wildcard
        42
        )
      != SQLITE_OK) {
    printf("\nCould not bind int.\n");
    RETURN 1;
  }

It is highly recommended to set a default password for the database as well to prevent SQL injection in SQL Server, from which the database administrator can utilize other functionalities of the database. There is a list of some common database applications and their default administrator accounts:

http://bobby-tables.com/php.html

https://phpdelusions.net/sql_injection

http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/12118602#12118602