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Β andQueryΒ 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 forandΒ based querieswhere 1=0Β for a starting point oforΒ based queries
- add a method
AddAndClauseΒ orAddOrClausetaking with parametersQuery,Β ΒFieldName,ParameterNameΒ andParameterValuethen whenParameterValueis 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]);
- for
- adds a parameter
Query.ParamByName(ParameterName).AsString := ParameterValue
- adds this to the SQL Text:
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 sonRobert'); 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
The Wiert Corner - irregular stream of stuff 