How to addslashes to values within apostrophe marks!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
fambi
Forum Newbie
Posts: 18
Joined: Sun Apr 02, 2006 12:12 am

How to addslashes to values within apostrophe marks!

Post 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.
User avatar
nickman013
Forum Regular
Posts: 764
Joined: Sun Aug 14, 2005 12:02 am
Location: Long Island, New York

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you shouldn't try to post process the string as it won't work well.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.)
fambi
Forum Newbie
Posts: 18
Joined: Sun Apr 02, 2006 12:12 am

Post 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.
User avatar
nickman013
Forum Regular
Posts: 764
Joined: Sun Aug 14, 2005 12:02 am
Location: Long Island, New York

Post by nickman013 »

Turn off magic quotes?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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
}
(#10850)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I've had something similar for a while in my snippets archive: http://timvw.madoka.be/programming/php/sql.txt
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
fambi
Forum Newbie
Posts: 18
Joined: Sun Apr 02, 2006 12:12 am

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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 :(
fambi
Forum Newbie
Posts: 18
Joined: Sun Apr 02, 2006 12:12 am

Post 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.
Post Reply