{"id":93,"date":"2009-01-27T16:06:00","date_gmt":"2009-01-27T16:06:00","guid":{"rendered":"https:\/\/knielsen-hq.org\/w\/?p=93"},"modified":"2021-09-01T11:08:48","modified_gmt":"2021-09-01T11:08:48","slug":"placeholders-and-sql-injection","status":"publish","type":"post","link":"https:\/\/knielsen-hq.org\/w\/placeholders-and-sql-injection\/","title":{"rendered":"Placeholders and SQL injection"},"content":{"rendered":"\n

It is sad to see how 9X% (or should that be 99.X%?) of SQL applications are riddled with SQL injection<\/a> bugs.<\/p>\n\n\n\n

There really is no excuse for this. Nobody writes code like this:<\/p>\n\n\n\n

sub stupid_sum {\n    my ($list) = @_;\n    my $string = shift @$list;\n    for (@$list) {\n      $string .= \" + \" . $_;\n    }\n    my $sum = eval($string);\n    return $sum;\n}\n<\/pre>\n\n\n\n
Right? Just because our computers use the Von Neumann architecture<\/a>, 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<\/a> we do not, but there is a reason TeX is not pleasant to write applications in).<\/p>\n\n\n\n
So when we use functions to group our code into logical units, we have this fancy syntax for something called parameters<\/em>. And we can write clever stuff like this:<\/p>\n\n\n\n
int foo(int a) {\n    return a + 1;\n}\n<\/pre>\n\n\n\n
And so the “+” and the “1” are part of the code<\/em> for the function foo()<\/code>, while the other value “a” to be added is data<\/em> which comes from another part of the program. Great stuff!<\/p>\n\n\n\n
In fact, in the old days, people were using something called embedded SQL<\/a>, 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.<\/p>\n\n\n\n
But there is no need for embedding the SQL into the syntax of every language, because these brilliant people invented the placeholder<\/em>! 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.<\/p>\n\n\n\n
And it is so easy. No need for mysql_real_quote_string()<\/code> and other horrors. Just do like this, using Perl and DBI as example:<\/p>\n\n\n\n
sub mark_items {\n    my ($dbh, $mark, @keys_list) = @_;\n    my $placeholders = join(\",\", map(\"?\", @keys_list));\n    $dbh->do(\"UPDATE t SET flag = ? WHERE id IN (\" . $placeholders . \")\",\n             undef,\n             $mark, @keys_list);\n}\n<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
As Bjarne Stroustrup<\/a> 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?<\/p>\n","protected":false},"excerpt":{"rendered":"
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 .= ” + ” . $_;… Continue reading Placeholders and SQL injection<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[4,8,31],"_links":{"self":[{"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/posts\/93"}],"collection":[{"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/comments?post=93"}],"version-history":[{"count":1,"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/posts\/93\/revisions"}],"predecessor-version":[{"id":94,"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/posts\/93\/revisions\/94"}],"wp:attachment":[{"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/media?parent=93"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/categories?post=93"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/knielsen-hq.org\/w\/wp-json\/wp\/v2\/tags?post=93"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}