The Sin Explained | Writing Secure Code

The most common variant of the sin is very simplean attacker provides your database application with some malformed data, and your application uses that data to build a SQL statement using string concatenation. This allows the attacker to change the semantics of the SQL query. People tend to use string concatenation because they dont know theres another, safer method. If they do know theres a better way, they dont use it because, lets be honest, string concatenation is easy, and calling other functions requires a little thought. We could say some developers are lazy, but we wont.

A less common variant is SQL stored procedures that take a parameter and simply execute the argument or perform the string concatenation with the argument and then execute the result.

Sinful C#

This is a classic example of SQL injection:

 using System.Data; using System.Data.SqlClient; ... string ccnum = "None"; try {  SqlConnection sql= new SqlConnection(@"data source=localhost;" +   "user id=sa;password=pAs$w0rd;");  sql.Open();  string sqlstring="SELECT ccnum" +  " FROM cust WHERE id=" + Id;  SqlCommand cmd = new SqlCommand(sqlstring,sql);  ccnum = (string)cmd.ExecuteScalar();  } catch (SqlException se) {  Status = sqlstring + " failed\n\r";  foreach (SqlError e in se.Errors) {  Status += e.Message + "\n\r";  } } catch (SqlException e) {  // OOops! }

The code below, also written in C#, is the same as the code above, but the SQL string is constructed using string replacement, not concatenation. It too is sinful.

 using System.Data; using System.Data.SqlClient; ... string ccnum = "None"; try {  SqlConnection sql= new SqlConnection(@"data source=localhost;" +   "user id=sa;password=pAs$w0rd;");  sql.Open();  string sqlstring="SELECT ccnum" +  " FROM cust WHERE id=%ID%";  String sqlstring2 = sqlstring.Replace('%ID%',id);  SqlCommand cmd = new SqlCommand(sqlstring2,sql);  ccnum = (string)cmd.ExecuteScalar();  } catch (SqlException se) {  Status = sqlstring + " failed\n\r";  foreach (SqlError e in se.Errors) {  Status += e.Message + "\n\r";  } } catch (SqlException e) {  // OOops! }

Sinful PHP

Here is the same kind of classic bungle, but this time written in another common language used for database access: PHP.

 <?php  $db = mysql_connect("localhost","root","$$sshhh...!");  mysql_select_db("Shipping",$db);  $id = $HTTP_GET_VARS["id"];  $qry = "SELECT ccnum FROM cust WHERE id =%$id%";  $result = mysql_query($qry,$db);  if ($result) {  echo mysql_result($result,0,"ccnum");  } else {  echo "No result! " . mysql_error();  } ?>

Sinful Perl/CGI

Here we go again, same defect, different language, this time in venerable Perl:

 #!/usr/bin/perl use DBI; use CGI; print CGI::header(); $cgi = new CGI;  $id = $cgi->param('id'); print "<html><body>"; $dbh = DBI->connect('DBI:mysql:Shipping:localhost',  'root',  'cre+')  or print "Connect failure : $DBI::errstr"; $sql = "SELECT ccnum FROM cust WHERE id = " . $id; $sth = $dbh->prepare($sql)  or print "Prepare failure : ($sql) $DBI::errstr"; $sth->execute()   or print "Execute failure : $DBI::errstr"; # Dump data while (@row = $sth->fetchrow_array) {  print "@row<br>"; } $dbh->disconnect; print "</body></html>"; exit;

Sinful Java and JDBC

Yet another commonly used language, Java. Its subject to the same kind of SQL injection security defect.

 import java.*; import java.sql.*;  ... public static boolean doQuery(String Id) {  Connection con = null;  try   {  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");  con = DriverManager.getConnection("jdbc:microsoft:sqlserver: " +  "//localhost:1433", "sa", "cre+");  Statement st = con.createStatement();  ResultSet rs = st.executeQuery(" SELECT ccnum FROM cust WHERE id = " + Id);  while (rs.next()) {  // Party on the query results  }  rs.close();  st.close();  }   catch (SQLException e)   {  // OOPS!  return false;  }  catch (ClassNotFoundException e2)   {  // Class not found  return false;  }   finally   {  try   {  con.close();  } catch(SQLException e) {}  }  return true; }

Sinful SQL

The example is not so common, but the author has seen it a couple of times in production code. This stored procedure simply takes a string as a parameter and executes it!

 CREATE PROCEDURE dbo.doQuery(@query nchar(128)) AS  exec(@query) RETURN

This, on the other hand, is much more common, and is just as dangerous:

 CREATE PROCEDURE dbo.doQuery(@id nchar(128)) AS  DECLARE @query nchar(256)  SELECT @query = 'select ccnum from cust where id = ''' + @id + ''''  EXEC @query RETURN

In the preceding example, the offending string concatenation is within the stored procedure. So youre still committing an atrocious sin, even with the correct high-level code calling the stored procedure.

Other SQL concatenation operators to look for are + and as well as the CONCAT() or CONCATENATE() functions.

In these small examples, the attacker controls the Id variable. Its always important to understand what the attacker controls to help determine whether there is a real defect or not. In these examples, the attacker completely controls the Id variable in the querystring, and because he can determine exactly what the querystring is, the results are potentially catastrophic.

The classic attack is to simply change the SQL query by adding more clauses to the query and comment out unneeded clauses. For example, if the attacker controls Id , he could provide 1 or 2>1 --, which would create a SQL query like this:

 SELECT ccnum FROM cust WHERE id=1 or 2>1 --

2>1 is true for all rows in the table so the query returns all rows in the cust table; in other words, the query returns all the credit card numbers . Note, we could use the classic "1=1" attack, but network admins tend to look for that in their intrusion detection systems (IDSs), so well use something different that flies beneath the radar, like 2>1 , thats just as effective.

The comment operator -- comments out any characters added to the query by the code. Some databases use -- and others use # . Make sure you know the comment operators for the databases you query.

There are numerous other attack variants too plentiful to cover here, so please make sure you refer to the Other Resources section for more examples.

Related Sins

All the preceding examples commit other sins as well:

Connecting using a high-privilege account
Embedding a password in the code
Telling the attacker too much when an error occurs

Taking each of these sins in order, all the samples connect using an administrative or high-privilege account, rather than an account with only the capability to access the database in question. This means the attacker can probably manipulate other assets in the database, or potentially the server itself. In short, a connection to a SQL database using an elevated account is probably a bug and violates the principal of least privilege.

Embedding passwords in the code is almost always a bad idea. See Sin 11 and Sin 12 for more information and remedies on this subject.

Finally, if any of the sample code fails, the error messages tells the attacker too much information. This information can be used to aid the attacker by disclosing the nature of the SQL query, or perhaps the name of objects in the database. See Sin 6 for more information and remedies.