The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,365 other followers

In this day and age, people still write SQL injection vulnerable code

Posted by jpluimers on 2018/03/20

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

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: