Welcome to WebmasterWorld Guest from 54.205.140.252

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Sanitizing user input - mysql real escape into a function?

     
5:39 pm on Sep 17, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:July 13, 2010
posts:170
votes: 0


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"?
6:51 pm on Sept 17, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


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
12:22 pm on Sept 18, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Feb 13, 2003
posts: 590
votes: 0


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
7:20 pm on Sept 23, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:July 13, 2010
posts:170
votes: 0


Thanks for the insights! :)
1:42 pm on Sept 24, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Apr 30, 2007
posts:1394
votes: 0


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.
8:46 pm on Sept 24, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:July 13, 2010
posts:170
votes: 0


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.
9:31 pm on Sept 24, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Apr 30, 2007
posts:1394
votes: 0


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.
10:27 pm on Sept 24, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:July 13, 2010
posts:170
votes: 0


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!