literal interpretations

I learned yesterday how to write SQL Injection attacks while learning how to code against them during data import. Vandalizing databases is surprisingly easy, as easy as writing email spam that pretends to be from anybody.

The mischief is similar to the old George Burns and Gracie Allen vaudeville act, where information that was intended as just a clause to a command is instead misinterpreted to be the actual command. The SQL Injection attack is constructed so as to cause a SQL database to misinterpret data in exactly the same way.

Here is the import data file that I constructed to cause the problem.

Name	Addr
Trust Me	.'); DROP TABLE TESTTABLE1; --

The single quote near the beginning is necessary so as to prematurely close the quotation that was intended to mark the end of a data field. The close parenthesis and semicolon finish out the proper syntax of the intended data import command. Next comes the new command that I want to "inject" as another valid SQL command. The double-dash at the end is also necessary, because it forces the database to ignore as a comment any additional text that comes afterwards, thereby masking the trailing characters from my original import command.

It really is that simple. Even xkcd made fun of the ease with which disastrous consequences can be implemented.



Here is the Powershell code that I used to take advantage of the SQL Injection attack during data import.

# SETUP
$conn = new-object ('Data.SqlClient.SqlConnection') 'Server=DBSERVERNAME;Integrated Security=True'
$conn.open()
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.CommandTimeout=30
$cmd.Connection = $conn

# SQL INJECTION
$input = import-csv g:\directory.path\sql1.injection.csv
foreach($row in $input) {
      $p1 = $row.name
      $p2 = $row.addr
      $sql = "INSERT INTO reporting.dbo.SampleTable (FullName, FullAddr) VALUES ('$p1','$p2');"
      $cmd.CommandText = $sql

      $out = $cmd.ExecuteNonQuery()
}

# CLEAN UP
$conn.close()
$conn = $null

And here is the code I used to replace the middle section so it is safe from SQL Injection.

# SAFE DATA IMPORT
$input = import-csv g:\directory.path\sql1.injection.csv
foreach($row in $input) {
      $p1 = $row.name
      $p2 = $row.addr
      $sql = 'INSERT INTO reporting.dbo.MasterID (FullName, FullAddr) VALUES (@p1,@p2);'
      $cmd.commandtext = $sql

      $p0=@{p1=$p1;p2=$p2}
      foreach($key in $p0.keys) {
            $cmd.parameters.addwithvalue("@$key",$p0[$key])
      }
      $out = $cmd.ExecuteNonQuery()
}

I used SQL Parameterization to separate the clauses (data fields) of my SQL statement from the actual SQL command itself. I used a powershell hash table to make the dictionary index for those data field values.

I tested it out, and it worked. The first coding sample caused the database to delete the test table. The second coding sample resisted the SQL Injection vandalism, and put the data values into the data fields as intended.