The best way to protect SQL injection?

[Alucard]

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]

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]

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]

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

[www.gerardvignes.com]

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/functio...ape-string.php

This takes the character set used by the database into account.

Gerard Vignes
http://www.GerardVignes.com
Seattle, WA

[Gordon Burditt]

>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]

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/functio...ape-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