|
davidj01 has asked for the wisdom of the Perl Monks concerning the following question:
Hi,
Would converting all meta characters (eg. | . ( ! etc.) to an html representation that is &#[ASCII CODE};
before saving to the mysql table prevent SQL Injection and good way to prevent the use of meta characters in
any call using system or open (eg sending email).
Example: Thus a single quote will be represented by & # 39; etc.
If I need any these characters, lets say to send email, I would simply reverse the conversion for the @ and period.
I'm interested in feedback.
Thanks
David J. |
Re: Prevent SQL Injection
by moritz (Cardinal) on Apr 07, 2008 at 15:10 UTC
|
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
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. | [reply]
[d/l]
[select] |
|
|
| [reply]
[d/l] |
|
|
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] |
|
|
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] |
|
|
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: Prevent SQL Injection
by Errto (Vicar) on Apr 07, 2008 at 17:59 UTC
|
In general you should store whatever data you receive in its original form, and not encoded for a particular output. For example, what if you need to send that text in a plain text email instead of an HTML email? What if you want to dump a bunch of rows of text into CSV?
Then when you need to use that text in a particular context, you can encode it appropriately. When you're sending it to the database, you should use placeholders, or if you must, use the quote method in DBI. When you're outputting it as HTML, then HTML-encode it. When you're passing it to a system call, apply shell escapes to it (is there a standard method for that?). If you're using it in a regex, use quotemeta or the \Q escape. And so on.
Update: andreas1234567's reply below is of course also correct.
| [reply]
[d/l]
[select] |
|
|
In general you should store whatever data you receive in its original form, and not encoded for a particular output.
Yes, provided that the data is properly validated, e.g. as described in Data Validation (owasp.org).
| [reply] |
|
|
Hi,
I've been updating my programs to use only place holders. I'm now looking at the next step.
"When you're outputting it as HTML, then HTML-encode it."
My question is naive but could you provide a couple of examples in order to clearly define what is meant by HTML-encode and in these examples show how one is handling any insecurities or difficult to display characters (I presuppose the single and dbl quote)
Thank-you
David J.
| [reply] |
Re: Prevent SQL Injection
by andreas1234567 (Vicar) on Apr 08, 2008 at 07:51 UTC
|
| [reply] |
|
|
