Page 1 of 1

How to addslashes to values within apostrophe marks!

Posted: Sun Apr 02, 2006 1:29 pm
by fambi
Hi all,

Let's say i have the following sql:

Code: Select all

$sql = "INSERT INTO table SET `field1`='$value1',  `field2`='$value2',  `field3`='$value3',  `field4`='$value4' ";
How can i run the $sql in a function that produces (in effect):

Code: Select all

$sql = "INSERT INTO table SET `field1`='".addslashes($value1)."',  `field2`='".addslashes($value2)."', `field3`='".addslashes($value3)."',  `field4`='".addslashes($value4)."' ";
Thanks for the help.

Posted: Sun Apr 02, 2006 1:39 pm
by nickman013
It would probably be easier if you removed the slashes in the code before the query.

I am not sure how to do that though.

Posted: Sun Apr 02, 2006 1:43 pm
by feyd
you shouldn't try to post process the string as it won't work well.

Posted: Sun Apr 02, 2006 1:44 pm
by timvw
I usually have an array with column => value pairs.. And then i use foreach to build my string

Code: Select all

foreach($row as $column => $value) {
  $value = mysql_real_escape_string($value);
  $sql .= "`$column`='$value, '";
}
For your example that goes like:

Code: Select all

for ($i = 1; $i <= 4; ++$i) {
  $value = mysql_real_escape_string({${'value' . $i});
  $sql .= "`${'field' . $i}`='$value', ";
}
(Don't forget to remove the last ", " characters.)

Posted: Sun Apr 02, 2006 1:50 pm
by fambi
Thanks for the input timvw.
feyd wrote:you shouldn't try to post process the string as it won't work well.
Not sure what you mean!

To explain my situation a bit more clearly, i am now faced with the daunting task of:

1. Going through EVERY SINGLE sql on our site.
2. Extracting the values from each sql.
3. Mysql_escaping each value.
4. Rewriting every sql to use the escaped values.

Fortunately, our sql's run through a wrapper class and so i am/was hoping to put together a function that sits within the wrapper and mysql_escapes the values of the the sql's that it processes.

Putting together this function (and any other advice you can offer) is where i need the help.

Thanks.

Posted: Sun Apr 02, 2006 1:53 pm
by nickman013
Turn off magic quotes?

Posted: Sun Apr 02, 2006 2:10 pm
by Christopher
fambi wrote:1. Going through EVERY SINGLE sql on our site.
Since you are going through all the SQL anyway, why don't you improve things a little by moving to prepared statements.

So, for example, you current code:

Code: Select all

$sql = "INSERT INTO table SET `field1`='$value1',  `field2`='$value2',  `field3`='$value3',  `field4`='$value4' "; 
$wrapper->query($sql);
Would change to:

Code: Select all

$sql = "INSERT INTO table SET `field1`=':1',  `field2`=':2',  `field3`=':3',  `field4`=':4' "; 
$wrapper->query($sql, $value1, $value2, $value3, $value4);
And all you need to do is modify the wrapper to check if values are passed. All the existing will still work while you are upgrading. Something like this:

Code: Select all

function query() {
  $args = func_get_args();
  $sql = array_shift($args);
  if (func_num_args() > 1) {
      $i = 1;
      foreach($args as $value) {
        $sql = str_replace(":$i", mysql_real_escape_string($value), $sql);
        ++$i;
      }
    }

// currrent code that uses
}

Posted: Sun Apr 02, 2006 5:22 pm
by timvw
I've had something similar for a while in my snippets archive: http://timvw.madoka.be/programming/php/sql.txt

Posted: Sun Apr 02, 2006 7:45 pm
by AKA Panama Jack
Remember that mysql_real_escape_string will only work on PHP 4.3.0 and higher. Earlier versions of PHP do not support that function.

Posted: Mon Apr 03, 2006 12:01 am
by fambi
Thanks to all of you for your input.

Either i haven't been able to be as clear as i can or i haven't been able to appreciate your suggestions.

Previously, addslashes was used to escape only those values that needed them for INSERT statements only, for example:

Code: Select all

$value1 = "I need lot's of help";
$value2 = 10493;
$sql = "INSERT INTO table SET field1='".addslashes($value1)."', field2='$value2' ";
//In this example, $value2 was not addslashed because it is an integer
But now, as part of php security, we've come to learn that EVERY value needs to be mysql_real_escaped regardless of the nature of the statement or the value.

This entails 'fixing' up every one of our sqls. At the moment, our site has hundreds (or thousands) of sqls and the thought of manually mysql_escaping the 10's of thousands of values is not very exciting and so, bearing in mind that the sql's pass through a wrapper, my intention is leave them alone (removing the addslashes from those that had them) and then pass the sql's through a function that uses a regular expression that does the following:

Code: Select all

function escape($sql)
{
  $escaped_sql = // Don't know what goes here;
  return $escaped_sql;
}

$old_sql = "INSERT INTO table SET field1='$value1', field2='$value2' "
$new_sql = escape($old_sql);
//which should return INSERT INTO table SET field1='".mysql_real_escapestring($value1)."', field2='".mysql_real_escapestring($value2)."'
So, what (i think) i need is help putting together the escape function.

Thanks for the help once again.

Posted: Mon Apr 03, 2006 12:07 am
by John Cartwright
the problem with your current setup, is that you will be passing the entire query through mysql_real_escape_string(), which is not what you want. You want to pass each individual variable.

If you download a copy of dreamweaver you can do a find all -> replace all call to search through many folders and replace the instances of addslashes() with mysql_real_escape_string()


other than that I think your out of luck :(

Posted: Mon Apr 03, 2006 12:10 am
by fambi
Jcart wrote:the problem with your current setup, is that you will be passing the entire query through mysql_real_escape_string(), which is not what you want. You want to pass each individual variable.
EXACTLY!!! This is what i want the function to do.

i.e. Take a string and mysql_escap those parts of the string which are quoted with apostrophes.