SQL injection

SQL injection is a security vulnerability that occurs in the database layer of an application. Its source is the incorrect escaping of variables embedded in SQL statements. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

Assuming the following code is embedded in the application, and a parameter "userName" that contains the user's name is given, SQL Injection is possible:

statement := "SELECT * FROM users WHERE name = '" + userName + "';"

If supplied with "a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%" as "userName", the following SQL statement would be generated:

SELECT * FROM users WHERE name = 'a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%';

The database would execute the statement in order, selecting data, dropping (deleting) the "users" table and selecting data that maybe was not meant to be displayed to web users. In essence, any data in the database available to the user connecting to the database could be read and/or modified.

Examples

SQL injection is easy to work around with in most programming languages that target web applications or offer functionality. In Perl DBI, the DBI::quote method escapes special characters (assuming the variable $sql holds a reference to a DBI object):

$query = $sql->prepare
  (
        "select * from users where name = "
    .
        $sql->quote($user_name)
   );

Or one may use the placeholder feature (with automatic quoting) as follows:

$query = $sql->prepare("select * from users where name = ?");
$query->execute($user_name);

In PHP, there are different built-in functions to use for different DBMSes. For MySQL, the equivalent is the built-in function mysql_escape_string:

$query_result = mysql_query
  (
        "select * from users where name = \""
    .
        mysql_escape_string($user_name)
    .
        "\""
   );

In the Java programming language, the equivalent is the PreparedStatement class.

Instead of

Connection con = (acquire Connection)
Statement stmt = con.createStatement();
ResultSet rset = stmt.executeQuery("SELECT * FROM users WHERE name = '" + userName + "';");

use the following

Connection con = (acquire Connection)
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM users WHERE name = ?");
pstmt.setString(1, userName);
ResultSet rset = stmt.executeQuery();

See also

• Code injection

External links

• Abusing Poor Programming Techniques in Webserver Scripts via SQL Injection
• Advanced SQL Injection Attacks
• What is SQL injection?
• Web Applications and SQL Injection
• What is an SQL Injection Attack / Vulnerability?
• Article "SQL Injection: Modes of Attack, Defence, and Why It Matters" by Stuart McDonald
• Article "SQL Server Attacks: Hacking, Cracking, and Protection Techniques" by Seth Fogie and Cyrus Peikari
• Article "SQL Injection and Oracle, Part One" by Pete Finnigan
• Article "SQL Injection Attacks – Are You Safe?" by Mitchell Harper
• Article "Protecting Against SQL Injection" by Umachandar Jayachandran
• Article "Stop SQL Injection Attacks Before They Stop You" by Paul Litwin
• "Injection Protection"
• "SQL injection Basic Tutorial"
• "SQLrand: Preventing SQL Injection Attacks" by Stephen W. Boyd and Angelos D. Keromytis