I keep being amazed that new generations of people keep writing SQL injection vulnerable code, so further below is a repeat of  [WayBack] xkcd: Exploits of a Mom on Little Bobby Tables named Robert '; Drop TABLE Students;--

Take this recent question on G+ for instance: [WayBack] Hi can you help to write correct Query for Filter 3 Data fields for Example Data1 , Data2 , Data2 txt1 = Data1 txt2= data2 txt3 = data3… – Jude De Silva – Google+ with this code fragment:

Tables:

Data1 , Data2 , Data2

Text control contents:

txt1 = Data1
txt2= data2
txt3 = data3

Examples when text property is filled:

ex1: Data1  and Data 3
ex2: Data 3 and Data2
ex3: Data 1, Data 2 Data 3

Code:

Qury.Close;
Query.Sql.Clear;
Qury.Sql.Add (Select * From Table1);
If Not (txt1.text = ' ')then
   Begin
   Qury.Sql.Add(Format ('Where Data1= ' '%s' ' ',[txt1] ));
  end;
If not (txt3.text = ' ') then
   Begin
   Qury.Sql.Add(Format ('and Data3= ' '%s' ' ',[txt1] ));
  end;

This example is wrong on so many levels, to lets explain a few:

• use name Qury and Query for queries: are they actually two variables?
• inconsistent keyword capitalisation for both used languages
• incinsistent indenting and unindenting
• mixed use of quotes for strings
• use of space for blank fields
• getting embedded quotes wrong

The basic solution for solving the actual problem asked is like this (assuming all user input are strings):

• use
  • where 1=1 for a starting point for and based queries
  • where 1=0 for a starting point of or based queries
• add a method AddAndClause or AddOrClause taking with parameters Query,  FieldName, ParameterName and ParameterValuethen when ParameterValue is not empty:
  • adds this to the SQL Text:
    • for and based queries:Format('and %s = :%s', [FieldName, ParameterName]);
    • for or based queries:Format('or %s = :%s', [FieldName, ParameterName]);
  • adds a parameter Query.ParamByName(ParameterName).AsString := ParameterValue

SQL Injection: Little Bobby Tables

Back in 2007, SQL Injection was already a very well known vulnerability (they date back to at least 1998), so Randall Munroe published [WayBack] xkcd: Exploits of a Mom on Little Bobby Tables named Robert '; Drop TABLE Students;--

School: “Hi, this is your son’s school. We’re having some computer trouble.”
Mom: “Oh, dear — Did he break something?”
School: “In a way. Did you really name your son Robert'); DROP TABLE Students;-- ?”
Mom: “Oh. Yes. Little Bobby Tables we call him.”
School: “Well, we’ve lost this year’s student records. I hope you’re happy.”
Mom: “And I hope you’ve learned to sanitize your database inputs.”
(Alt-text: “Her daughter is named Help I’m trapped in a driver’s license factory.”)

It did not just get explained at [WayBack] 327: Exploits of a Mom – explain xkcd (Explain xkcd is a wiki dedicated to explaining the webcomic xkcd. Go figure.), Little Bobby Tables got his own page there: [WayBack] Little Bobby Tables – explain xkcd.

Like people continuing writing SQL injection vulnerable code, XKCD posted another SQL injection in [WayBack] 1253: Exoplanet Names – explain xkcd by using e'); DROP TABLE PLANETS;-- as name for Planet e of Star Gliese 667.

Preventing SQL Injection

A few years later, around 2009, Bobby Tables inspired [WayBack] bobby-tables.com: A guide to preventing SQL injection explaining:

• what not to do “Don’t try to escape invalid characters. Don’t try to do it yourself.”
• what do to: “Learn how to use parameterized statements. Always, every single time.”

bobby-tables.com

It goes on with many examples of parameterised queries in many environments and language, for instance in the language used above: Delphi.

You can contribute new environments and languages as the site has source code at [WayBack] GitHub – petdance/bobby-tables: bobby-tables.com, the site for preventing SQL injections.

Finally, it points to a few more resources:

• [WayBack] SQL Injection Myths and Fallacies by Bill Karwin
• [WayBack] How to Write Injection-Proof SQL by Bruce Schneier
• [Archive.is/WayBack] Defending Against SQL Injection Attacks by Oracle (likely a lot older)
• [WayBack] Detecting Postgres SQL Injection by Greg Sabino Mullane

WayBack bobby-tables.com: A guide to preventing SQL injection in Delphi

Delphi

To use a prepared statement, do something like this:

query.SQL.Text := 'update people set name=:Name where id=:ID';
query.Prepare;
query.ParamByName( 'Name' ).AsString := name;
query.ParamByName( 'ID' ).AsInteger := id;
query.ExecSQL;

–jeroen

Read the rest of this entry »