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 querieswhere 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 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 »
The Wiert Corner - irregular stream of stuff 