Re: Prevent SQL Injection

The right way to prevent SQL injection is to use placeholders and DBI:

use DBI;
my $dbh = DBI->connect(...);
# WRONG my $insert = $dbh->prepare("INSERT INTO my_table VALUES(my_col
+umn)");
# Thanks Narveson!
my $insert = $dbh->prepare("INSERT INTO my_table (my_column) VALUES( ?
+ )");

my $evil_string = q{"'|?°*;--};
$insert->execute($evil_string); # no problem
[download]

HTML escaping isn't the universal solution because different output formats use different quoting mechanisms, and your example system call wouldn't know that ' is a single quote.

So for all other applications you have to escape individually, so storing quoted strings in the DB isn't really helpful.

Comment on Re: Prevent SQL Injection Select or Download Code

Replies are listed 'Best First'.
Re^2: Prevent SQL Injection by Narveson (Chaplain) on Apr 07, 2008 at 15:20 UTC
Shouldn't the example include a placeholder? `my $insert = $dbh->prepare("INSERT INTO my_table (my_column) VALUES( ? )");`	[reply] [d/l]
Re^2: Prevent SQL Injection by davidj01 (Novice) on Apr 07, 2008 at 15:34 UTC
Hi, >different output formats use different quoting mechanisms, >and your example system call wouldn't know that ' is a >single quote. For example? Thanks so far David J	[reply]
Re^3: Prevent SQL Injection by moritz (Cardinal) on Apr 07, 2008 at 15:50 UTC
For example, one day you might want to produce PDFs from your database instead of HTML, and I suspect PDF has a completely different escaping mode. When you escape for the shell you have to insert a backslash before a single quote to escape it. Very silly example: You want to use grep (the command) to search for a substring in a large text file. If you search for the numeric entity, while the text file contains the literal character. File names have a completely different syntax. If you escape a file name under unix, you have to escape whitespaces - but surely you don't wan to escape all whitespaces in your database? (it would kill a word based full text index, for example). Even when you do HTML escape only, you have to very careful: there's more than one way to escape most characters. Many characters have names, so you can use `ä`, and numeric `Ӓ` (and I think also a hexadecimal escape as well). So if your comparisons for text equality should really work, you have to define a canonical form and translate everything into that form. D'oh.	[reply] [d/l] [select]
Re^3: Prevent SQL Injection by apl (Monsignor) on Apr 07, 2008 at 16:15 UTC
This is off-topic, but is intended to be helpful to you in the future. You had posted: Hi, >different output formats use different quoting mechanisms, >and your example system call wouldn't know that ' is a >single quote. For example? Thanks so far David J If you had made use of <blockquote> and </blockquote>, your post would have looked like: Hi, different output formats use different quoting mechanisms, and your example system call wouldn't know that ' is a single quote. For example? Thanks so far David J This is slightly more readable. (Remember, if it looks odd to you when you Preview a post, imagine how it looks to us.)	[reply]
Re^4: Prevent SQL Injection by davidj01 (Novice) on Apr 07, 2008 at 16:58 UTC
Hi, Got it Thank-you perlmonks of wisdom. David J	[reply]