SQL Injection

SQL injection is a security vulnerability that occurs in the database layer of an application. Its source is the incorrect escaping of dynamically-generated string literals 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.

[edit]

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_real_escape_string:

$query_result = mysql_query

  (

        “select * from users where name = \””

    .

        mysql_real_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 = pstmt.executeQuery();

In the .NET (or Mono) programming language “C#”, the equivalent are the ADO.NET SqlCommand (for Microsoft SQL Server) or OracleCommand (for Oracle’s database server) objects. The example below shows how to prevent injection attacks using the SqlCommand object. The code for other ADO.NET providers is very similar, but may vary slightly depending on the specific implementation by that provider vendor.

Instead of

using( SqlConnection con = (acquire connection) ) {

    con.Open();

    using( SqlCommand cmd = new SqlCommand(“SELECT * FROM users WHERE name = ‘” + userName + “‘”, con) ) {

       using( SqlDataReader rdr = cmd.ExecuteReader() ){

           …

       }

    }      

}

use the following

using( SqlConnection con = (acquire connection) ) {

    con.Open();

    using( SqlCommand cmd = new SqlCommand(“SELECT * FROM users WHERE name = @userName”, con) ) {

   

       cmd.Parameters.Add(“@userName”, userName);

 

       using( SqlDataReader rdr = cmd.ExecuteReader() ){

           …

       }

    }      

}

[edit]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: