Auto-escaping variables via EzSQL

Hi all,

In case it may be useful for someone, I made this ad-hoc solution to allow auto-escaping of variables. The advantage of this approach is that one can embed the variables directly within the query (not needing to pass the variables separately). The only thing you have to do is add a backslash before the variable. I've written it for MySQL, but I think you should be able to use the approach to add this for other databases that allow such escaping.

This is pretty much my first time doing anything with object oriented code, so my approach may be off base (let me know). But, it does seem to work. This approach has one disadvantage though; any variables to be escaped in this way have to be declared global if they are within a function, as must your object itself. This may limit its use for some people, but to get this ease of use, I really couldn't find any other way to implement it.

Steps to modify the EzMySQL class file:

1) In the very beginning of the EzMySQL class file (before the class is delineated)--e.g., before the $ezsql_mysql_str array is declared, add the following line:

$ezsql_alrd_mtchd = array();

2) Find this line (the "constructor") within the EzMySQL class at the beginning

function ezSQL_mysql($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost')

3) Replace it with this line:
function ezSQL_mysql($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost', $me="")

4) Find these lines:

$this->dbuser = $dbuser;
$this->dbpassword = $dbpassword;
$this->dbname = $dbname;
$this->dbhost = $dbhost;

5) Put these lines after those in step 4 (you can comment out the above too):

$this->me = $me;
$this->quick_connect($dbuser, $dbpassword, $dbname, $dbhost);

6) In the last "else" of the connect function, add this line (anywhere on its own line):

$this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword,true);

7) Add this function (after the escape function is a logical place):

/**********************************************************************
* Auto-format a mySQL string correctly for safe mySQL insert
* (no matter if magic quotes are on or not)
*/

function auto_escape($matches, $me)
{
global $$matches[2];
global $ezsql_alrd_mtchd;
global $$me;
if (!in_array($$matches[2], $ezsql_alrd_mtchd)) {
$$matches[2] = mysql_real_escape_string(stripslashes($$matches[2]), $$me->dbh);
$ezsql_alrd_mtchd[] = $$matches[2];
} // end if
return $$matches[2].$matches[3];
}


8) Find these two lines (in the query function)

// Keep track of the last query for debug..
$this->last_query = $query;

9) Put this code somewhere before the lines in step 8:

$esc = "\\$";
$query = preg_replace_callback("@(\{{0,1}{$esc})([a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*)\}{0,1}(.*?)@",
create_function(
'$matches',
'return call_user_func_array(array("ezSQL_mysql", "auto_escape"), array($matches, "'.$this->me.'"));'),
$query
);

Then whenever you use the class, you must do the following:

1) Since there is no easy way (that I could find) to obtain the name of the current object from within a class (maybe reflection?), you'll need to add this one parameter to your ezSQL_mysql database instantiation, just putting in string format the name of the object variable (without the $). For example:

$db_blo = new ezSQL_mysql($user, $password, $database, $host, "db_blo");

2) Now the only other thing you have to do is add a backslash to each variable (that you want escaped) in your queries. For example:

$db_blo->get_results("SELECT * FROM $mytable WHERE option = '\$user_choice'")

In this case, the variable $user_choice will be escaped, using the appropriate mysql_real_escape_string() automatically.

Feedback or questions are welcome... Remember, I'm a relative novice at this, so use at your own peril! :)