Sanitizing user input - mysql real escape into a function?

[brokaddr]

For speed's sake, should mysql_real_escape_string() be used in a function?

For example:

 function tep_sanitize_string($string) {
$patterns = array ('/ +/','/[<>]/');
$replace = array (' ', '_');
return preg_replace($patterns, $replace, trim($string));
 }

(I know that is a different method of sanitization, but it's an example.)

Or is it more efficient to use like so:

$name = mysql_real_escape_string($_POST['name']);

And, what about filtering %'s?
What is preventing the user from doing a select:

$strip = mysql_real_escape_string($_POST['name']);

.. and I write: %ary in the box..

mysql_query('SELECT * FROM names WHERE name LIKE "'.$strip%'"'); 

.. to pull all names like "Gary", "Bary" or "Hary"?

[Matthew1980]

Hi there brokaddr,

Well firstly I would think that using mysql_real_escape_string() on it's own would be the best option, bearing in mind that you need a DB connection handle around to actually make this function work; also in your example your calling preg_ functions so there are slight overheads there.

I personally put all my sanitising functions into one custom function and call it when I need it, but I use array map (using the ca;;back feature) so that I apply the selected functions to all the elements of any array that I put through it..

For your second question, not really sure, never thought of that scenario before, I suppose that if I wanted to have a more advanced search filter option on my site, that would be a good method; but there are probably more ways of doing the same thing directly through sql functions, though I haven't come across them..

Hope that helps a little.

Cheers,
MRb

[sonjay]

Well, the user doesn't get to write the actual query. You write the actual query, and if you're using LIKE then it must be your intention to allow the user to do a wildcard search.

If your query uses "WHERE name ='name'" then the user who enters %ary will only find users named %ary

[brokaddr]

Thanks for the insights! :)

[enigma1]

Since you are using osCommerce, utilize the mysql wrappers the framework has, tep_db_query instead of mysql_query. There are 2 filtering functions in the database file. tep_db_prepare_input and tep_db_input. The tep_db_input invokes the mysql_real_escape_string. For user data input use the tep_db_prepare_input that sanitizes strings that can be used with forms. Ideally when you use the wrappers, combine the 2 like:

$strip = tep_db_prepare_input($_GET['strip']);
$sql_query = tep_db_query("select * from " . TABLE_NAMES . " where name like '%" . tep_db_input($strip) . "%'");
if( tep_db_num_rows($sql_query) ) {
// Fetch the rows
}

So if the $strip var is used with a form it is also sanitized at the HTML level.

[brokaddr]

A while back I added custom code using the tep_db_prepare_input() to filter user input, but individuals with apostrophes were able to break the page using O'Toole type names. Not sure why, as I had always assumed tep_db_prepare_input() sanitized everything. I've been hesitant on using it ever since.

[enigma1]

Make sure you use the tep_db_prepare_input for HTML fields. For mysql the tep_db_input should be used last. I am using both for ages and it's what makes the difference against injections in the framework.

Also you don't want to use tep_db_prepare_input for safe data (like when you retrieve fields directly from the database) because it will try to sanitize the strings.

[brokaddr]

For mysql the tep_db_input should be used last.

This is probably what I missed & why they weren't cleansed properly! I'll have to remember this for next time. :) Thanks!