You are viewing kristiannielsen

Kristian Nielsen - Placeholders and SQL injection
January 27th, 2009
04:06 pm

[Link]

Previous Entry Share Next Entry
Placeholders and SQL injection

It is sad to see how 9X% (or should that be 99.X%?) of SQL applications are riddled with SQL injection bugs.

There really is no excuse for this. Nobody writes code like this:

sub stupid_sum {
    my ($list) = @_;
    my $string = shift @$list;
    for (@$list) {
      $string .= " + " . $_;
    }
    my $sum = eval($string);
    return $sum;
}
Right? Just because our computers use the Von Neumann architecture, where CPU instructions and data is stored in the same memory, does not mean that we cannot distinguish between code and data (ok, so in TeX we do not, but there is a reason TeX is not pleasant to write applications in).

So when we use functions to group our code into logical units, we have this fancy syntax for something called parameters. And we can write clever stuff like this:

int foo(int a) {
    return a + 1;
}
And so the "+" and the "1" are part of the code for the function foo(), while the other value "a" to be added is data which comes from another part of the program. Great stuff!

In fact, in the old days, people were using something called embedded SQL, which tries to keep this distinction for using SQL with another language. Though I have to admit, having used Oracle ProC, that this was quite horrible.

But there is no need for embedding the SQL into the syntax of every language, because these brilliant people invented the placeholder! So now we can also have parameters for SQL, an SQL string holds the code to be executed, and placeholders supply the values to be used from other parts of the program.

And it is so easy. No need for mysql_real_quote_string() and other horrors. Just do like this, using Perl and DBI as example:

sub mark_items {
    my ($dbh, $mark, @keys_list) = @_;
    my $placeholders = join(",", map("?", @keys_list));
    $dbh->do("UPDATE t SET flag = ? WHERE id IN (" . $placeholders . ")",
             undef,
             $mark, @keys_list);
}
And then you can sleep well at night not worrying about which kind of values are passed to your SQL, or whether "+" can maybe format your hard disk if someone passes in the wrong argument.

Just because modern dynamic languages make string concatenation easy, does not mean that confusing code and data is a good thing. Von Neumann architecture is good for CPUs, but at higher levels of abstraction we have moved on.

As Bjarne Stroustrup often says, just as plumbers need an education to be allowed to mess up pipes, why doesn't a programmer need an education that makes him or her understand these things before being allowed to release software?

Tags: , ,

(6 comments | Leave a comment)

Comments
 
From:(Anonymous)
Date:January 27th, 2009 03:51 pm (UTC)

Stroustrup

(Link)
> As Bjarne Stroustrup often says, just as plumbers need an education to be allowed to mess up pipes, why
> doesn't a programmer need an education that makes him or her understand these things before being
> allowed to release software?

This is my favorite Stroustrup quote, actually. I mean, just look at his language. Oh man. :)

But, btw, I agree with you, however it might've sounded like on drizzle-discuss ;)

best,
-k
From:(Anonymous)
Date:January 27th, 2009 04:49 pm (UTC)
(Link)
"It is sad to see how 9X% (or should that be 99.X%?)"

Nah, it isn't that bad...it's more like one out of 10 or 20 (which is still quite bad of course)
From:bkarwin
Date:January 27th, 2009 09:06 pm (UTC)

Placeholders for data only

(Link)
I'm an advocate of using parameter placeholders too, but to be fair we should acknowledge that parameters can be used only in place of constant data values in SQL expressions. If you have any other part of your query that you need to be dynamic, you still have to resort to string interpolation. For example, if you want to ORDER BY a user-selectable column.

The best way to avoid SQL injection is to validate external input as strictly as possible, and then escape it as you interpolate into the SQL string. Many database API have a function to escape data values such as mysql_real_escape(), but I don't usually see functions for applying delimiters for table or column identifiers.
From:kristiannielsen
Date:January 28th, 2009 07:08 am (UTC)

Re: Placeholders for data only

(Link)
There is no need to quote user-selectable columns. Instead do this:

my $columns = { 1 => "create_dt", 2 => "type", 3 => "username" };
die unless exists($columns->{$user_choice});
my $sort_column = $columns->{$user_choice}";
my $sql = "SELECT * FROM t ORDER BY $sort_column";
From:peter.makholm.net
Date:January 28th, 2009 07:51 am (UTC)

Using the eval() fuction

(Link)
Placeholders are great for inserting tainted data in code before evaluation. And for writing SQL queries this is often all you need to getting the work done.

In other cases being able to generate code, compile it, and run it on the fly (as in you stupid_sum) is a quite powerful technique. But of course you have to make sure that data tainted by outside users doesn't leak into unwanted places. But as Stroustrup implies, you have to know what you're doing.
From:kristiannielsen
Date:January 28th, 2009 08:55 am (UTC)

Re: Using the eval() fuction

(Link)
Agree.

In Perl terms, my point can be expressed succinctly as follows:

Don't use eval "..." when you can use eval { ... }

:-)
Powered by LiveJournal.com