Re: Detect SQL injection

DBI does the quoting for you. You should never use sprintf or other string formatting because the likeliness that your quoting is not sufficient is too big. Other people have solved that issue for you decades ago once and forever.

I have to admit this is untested, but it is very likely to work like this.

$dbh->do('CREATE TABLE test (a ?, b INT)', undef, $SQL);
[download]

If not, have a look at http://search.cpan.org/~timb/DBI-1.613/DBI.pm#quote Or consolidate your SQL driver for quoting functions if you don't use DBI (Dare you!).

Regards
Tilman Baumann

Comment on Re: Detect SQL injection Download Code

Replies are listed 'Best First'.
Re^2: Detect SQL injection by JavaFan (Canon) on Aug 10, 2010 at 11:31 UTC
A few points. Not every database(driver) allows place holders in just any place you want. You do not want to quote your types. Even simple types usually consist of several (lexical) tokens, for instance `char(3)`, which has 4 tokens. Quoting that makes it one token, and not valid SQL. Why the Dare you! at the end? There can be many reasons to prefer more closely following the companies database's API instead of using a greatest-common-divisor strategy. I've done rapid prototyping, trying out solution using Perl, which then later got implemented in the company's main code repo, which was written in C. I wrote my solutions using sybperl, and DBlib/CTlib calls, because those are the calls the Sybase C-libraries provided. DBI would not have been a good choice.	[reply]
Re^2: Detect SQL injection by jeanluca (Deacon) on Aug 10, 2010 at 14:31 UTC
Thats what I was looking for. On the other hand, I just tried to execute a possible SQL-injected query `$dbh->do( 'CREATE TABLE a (x INT) ; DROP TABLE a -- );' ) ; // or $dbh->do( 'CREATE TABLE a (x INT) ; DROP TABLE a' ) ;` [download] Both give an error. Is it a rookie SQL mistake or what. I'm confused now!!	[reply] [d/l]