Home > Archive > PHP Programming > December 2006 > The best way to protect SQL injection?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
The best way to protect SQL injection?
|
|
| Alucard 2006-12-26, 8:00 am |
| Hi all.
I would like to ask if Regular expression is the best way to deal with
SQL injection attack, and no mysql_real_escape_string() is used:
if(preg_match("[A-Za-z0-9](4,6)")){
print "Success!";
}
In the above example, only character and digit are allowed. Other
injection technique is no used.
Is it correct? Did I make any foolish assumptions or mistakes? Please
let me know.
Thank you very much.
| |
| Tim Van Wassenhove 2006-12-26, 8:00 am |
| Alucard schreef:
> Hi all.
>
> I would like to ask if Regular expression is the best way to deal with
> SQL injection attack, and no mysql_real_escape_string() is used:
Imho there are two things you have to take care of:
1) Validate user input (a regular expression can be used)
2) Prepare the data for use in a MySQL query (mysql_real_escape_string
can be used for but these day's i'd opt for parameter binding instead...)
--
Tim Van Wassenhove <url:http://www.timvw.be/>
| |
| www.gerardvignes.com 2006-12-26, 7:01 pm |
| I wrote a simple PHP function for handling string arguments to a SQL
Query:
function SqlEscapedQuotedString($unescaped_string
) {
return '"' . addslashes($unescaped_string) . '"';
}
I never accept a SQL Query from the client, only an Argument to a SQL
Query on the server.
Gerard Vignes
http://www.GerardVignes.com
Seattle, WA
| |
| Dikkie Dik 2006-12-26, 7:01 pm |
| > I wrote a simple PHP function for handling string arguments to a SQL
> Query:
>
> function SqlEscapedQuotedString($unescaped_string
) {
> return '"' . addslashes($unescaped_string) . '"';
> }
For me, this is way too simple.I use "whitelisting" for the SQL values:
any character that is valid SQL is allowed (though escaped for some
characters), and a string containing any other character is sent as a
hexadecimal string.
Best regards
| |
|
|
| Gordon Burditt 2006-12-26, 10:00 pm |
| >I would like to ask if Regular expression is the best way to deal with
>SQL injection attack, and no mysql_real_escape_string() is used:
>
>if(preg_match("[A-Za-z0-9](4,6)")){
> print "Success!";
>}
>
>In the above example, only character and digit are allowed. Other
You mean letter and digit, don't you?
Certain characters (e.g. single quote, double quote, backslash) are
ones that cause trouble.
>injection technique is no used.
Your approach will not work where valid input (e.g. of human names)
includes characters which need to be escaped (e.g. 'Miles O'Brien')
and spaces. On the other hand, it may work fine (if you change the
length limit) for inputting license plate numbers and possibly
product serial numbers. It will NOT work for inputting serial
numbers on US currency, which sometimes contain '*' as the last
character.
>Is it correct? Did I make any foolish assumptions or mistakes? Please
>let me know.
| |
| Dikkie Dik 2006-12-27, 6:59 pm |
| > The suggested way to protect user-supplied input to with MySQL involves
> using a special PHP function for MySQL:
>
> mysql_real_escape_string (PHP 4 >= 4.3.0, PHP 5)
>
> http://www.php.net/manual/en/functi...cape-string.php
>
> This takes the character set used by the database into account.
I know. And that is a severe problem for me. At the time I build the
queries, there may not even be a database connection. I do not want it
to work with a current database connection, I want it to work with _all_
database connections. SQL itself is just normal 7-bits ASCII (there may
be ways to configure the server otherwise, but I don't do that) and it
is only the strings that have to be escaped. So what is safer than
building the entire command in 7-bits ASCII?
Best regards
|
|
|
|
|