1

I needed a generic function in php that will properly clean and escape any variable used in a Dynamic MySQL Statement. For example MySQL is vulnerable to random user - inserted data. Any sample code , or links are highly appreciated.

Edit 1- I did follow the links posted below. I still feel a concrete example would help.The requirement at work is to have a function which ma look like below:

function MySQLClean($string){
    // Contentns
    return string; 
}

My questions are

  1. What characters should this function escape for mysql . I know a few like ' ^ etc
  2. What characters should be removed i.e cleaned ?. This should be generic rather than databsae specific.
  3. How do I test it ? - Do , I pass in each string that make up my query to this function before executing the query or do I pass in the entire query to this function , split them into tokens and then clean/escape each character in the tokenized string and return it by joining it together.

An example of a Before and After "Escaping and Cleaning" the query string will be highly appreciated.

If this explanation seems vague and unspecific - that pretty much sums up my understanding of how to clean and validate the data. I will however be glad to provide any further details.

Edit 2 - After reading some material on the net and following the link in the given below answers - I have the below following function 

function MySQLClean($string)
{
 if(get_magic_quotes_gpc()){

    $string = stripslashes($string); 

 }

 return addcslashes(mysql_real_escape_string($string),"%_");
}

Is this sufficient?

Eternal Learner
  • 3,800
  • 13
  • 48
  • 78
  • possible duplicate of [Best way to stop SQL Injection in PHP](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php) - And that's a highly ranked question, and has a bunch of good answers... – ircmaxell Nov 18 '10 at 19:07

3 Answers3

2

If you use prepared statements, your data will be cleaned and help prevent SQL injection attacks.

Shawn Steward
  • 6,773
  • 3
  • 24
  • 45
  • 1
    Is that a generic answer? Prepared statement got to be used smartly, not randomly. – Shoe Nov 18 '10 at 19:07
  • @Charlie Pigarelli: Using bound parameters is the smart way, as it's a systematic feature. Escaping for SQL concatenation however is susceptible to omission. (Exhibit: Wordpress) – mario Nov 18 '10 at 22:08
2

http://www.php.net/manual/en/function.mysql-real-escape-string.php

Jan Thomä
  • 13,296
  • 6
  • 55
  • 83
1

Ok, since you've edited your question and I better understand what you're trying to do, let me say this:

Don't Do It!

You will run into problems with the character set of the connection, differing collations, etc. There are a fair number of edge cases that you will likely miss and still be vulnerable with. For one example of an edge case, check out Chris Shiflett's Blog Post...

If you're writing a DB abstraction layer and want to create a uniform interface, call the database's escape method in the driver layer. Don't try to write your own escape mechanism since it will not be nearly as good as the in-built one, and will not be kept up to date as well either...

ircmaxell
  • 163,128
  • 34
  • 264
  • 314