Differences

This shows you the differences between two versions of the page.

--- sql_injection:primary_defenses [2016/10/13 13:25] – peter
+++ sql_injection:primary_defenses [2020/04/16 20:53] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== SQL Injection - Primary Defenses ======
-===== Primary Defenses =====
-  * Use Prepared Statements (Parameterized Queries)
-  * Use Stored Procedures
-  * Escape all User Supplied Input
-===== Prepared Statements =====
-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.
-==== PHP ====
-=== Using mysqli ===
-The MySQL Improved extension handles bound parameters.
-<code php>
-$stmt = $db->prepare('update people set name = ? where id = ?');
-$stmt->bind_param('si',$name,$id);
-$stmt->execute();
-</code>
-or
-<code php>
-$stmt = $dbh->prepare('update mb_users set password = ? where username = ?');
-$dbh->execute($stmt, array('12345', 'bob'));
-</code>
-=== Using ADODB ===
-ADODB provides a way to prepare, bind and execute all in the same method call.
-<code php>
-$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'])
-);
-</code>
-=== Using the ODBC layer ===
-<code php>
-$stmt = odbc_prepare( $conn, 'SELECT * FROM users WHERE email = ?' );
-$success = odbc_execute( $stmt, array($email) );
-</code>
-or:
-<code php>
-$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));
-</code>
-=== Using the PDO layer ===
-Here's the long way to do bind parameters.
-<code php>
-$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();
-</code>
-And a shorter way to pass things in.
-<code php>
-$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) );
-</code>
-=== Using PostgreSQL ===
-<code php>
-$result = pg_query_params( $dbh, 'SELECT * FROM users WHERE email = $1', array($email) );
-</code>
-==== Java ====
-=== JDBC ===
-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.
-<code java>
-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()){
-    //...
-}
-</code>
-=== Hibernate ===
-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.
-<code java>
-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();
-</code>
-==== Perl ====
-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:
-<code perl>
-$sth = $dbh->prepare("SELECT * FROM users WHERE email = ?");
-foreach my $email (@emails) {
-    $sth->execute($email);
-    $row = $sth->fetchrow_hashref;
-    [...]
-}
-</code>
-However, you can't use parameterization for identifiers (table names, column names) so you need to use DBI's quote_identifier() method for that:
-<code perl>
-# 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 ...");
-</code>
-You could also avoid writing SQL by hand by using DBIx::Class, SQL::Abstract etc to generate your SQL for you programmatically.
-==== SQLite ====
-Use **sqlite3_prepare()** to create a statement object.
-<code sql>
-  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,
-  // 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,
-,  // Index of wildcard
-.2
-        )
-      != SQLITE_OK) {
-    printf("\nCould not bind double.\n");
-    return 1;
-  }
-  if (sqlite3_bind_int(
-        stmt,
-,  // Index of wildcard
-        )
-      != SQLITE_OK) {
-    printf("\nCould not bind int.\n");
-    return 1;
-  }
-</code>
-===== Escape all User Supplied Input =====
-**<color red>WARNING</color>**:  Escaping is inadequate to prevent SQL injection, use prepared statements instead!
-See: https://paragonie.com/blog/2015/05/preventing-sql-injection-in-php-applications-easy-and-definitive-guide
-=== Alternative Method ===
-**TODO** : Check if this works?
-<nowiki>It seems to me that it's sufficient to escape single quotes with single quotes, ie. '' (2 single quotes) instead of \'.</nowiki>
-For example,
-<code mysql>
-$mysql['username'] = str_replace(array('\\', '\''), array('\\\\', '\\\''), $_POST['username']);
-$mysql['password'] = str_replace(array('\\', '\''), array('\\\\', '\\\''), $_POST['password']);
-</code>
-will make the injection attempt successful, whereas
-<code mysql>
-$mysql['username'] = str_replace(array('\\', '\''), array('\\\\', '\'\''), $_POST['username']);
-$mysql['password'] = str_replace(array('\\', '\''), array('\\\\', '\'\''), $_POST['password']);
-</code>
-will make the injection attempt fail.
-Isn't the latter method (str_replace(array('\\', '\''), array('\\\\', '\'\''), $data)) always sufficient for preparing data for a mysql query where apostrophe is the enclosure character?  If so, is there any reason why we should still rather be using mysql_real_escape_string()?
-===== Safe Examples =====
-The following examples are safe:
-<code php>
-mysql_set_charset($charset);
-mysql_query("SET SQL_MODE=''");
-$var = mysql_real_escape_string('" OR 1=1 /*');
-mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');
-</code>
-...because we've explicitly selected an SQL mode that doesn't include NO_BACKSLASH_ESCAPES.
-<code php>
-mysql_set_charset($charset);
-$var = mysql_real_escape_string("' OR 1=1 /*");
-mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");
-</code>
-...because we're quoting our string literal with single-quotes.
-<code php>
-$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
-$stmt->execute(["' OR 1=1 /*"]);
-</code>
-...because PDO prepared statements are immune from this vulnerability (and ircmaxell's too, provided either that you're using PHP≥5.3.6 and the character set has been correctly set in the DSN; or that prepared statement emulation has been disabled).
-<code php>
-$var  = $pdo->quote("' OR 1=1 /*");
-$stmt = $pdo->query("SELECT * FROM test WHERE name = $var LIMIT 1");
-</code>
-...because PDO's quote() function not only escapes the literal, but also quotes it (in single-quote ' characters); note that to avoid ircmaxell's bug in this case, you must be using PHP≥5.3.6 and have correctly set the character set in the DSN.
-<code php>
-$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
-$param = "' OR 1=1 /*";
-$stmt->bind_param('s', $param);
-$stmt->execute();
-</code>
-...because MySQLi prepared statements are safe.
-===== References =====
-http://bobby-tables.com/php.html
-https://phpdelusions.net/sql_injection