Page 1 of 1

need to replace single quotes in a query string with \'

Posted: Wed Sep 12, 2007 1:13 pm
by Burrito
I have a bunch of query strings that I need to replace single quotes in the values with escaped quotes:

ie:

Code: Select all

INSERT INTO dirlist (dept, title, fname, lname, phone, fax, email) VALUES ('President's Office','DEPT','Grand Canyon University','','602-639-6200','','');
needs to become:

Code: Select all

INSERT INTO dirlist (dept, title, fname, lname, phone, fax, email) VALUES ('President\'s Office','DEPT','Grand Canyon University','','602-639-6200','','');
** note the president's office is now president\'s office.

I have tried using this pattern a lot of derivatives of it to no avail:

Code: Select all

$pattern = "#^[\('|'\)|',|,'](')#";
can anyone shed some light or help me out with a working pattern?

thanks

Burr

Posted: Wed Sep 12, 2007 1:54 pm
by Benjamin
Maybe you could replace all the single quotes that have an alpha character after them and manually change the ones that were missed.

Posted: Wed Sep 12, 2007 2:48 pm
by GeertDD
Replace all single quotes that are not preceded nor followed directly by parentheses or comma's.

Code: Select all

preg_replace('/(?<![(),])\'(?![(),])/', '\\\'', $str);

Posted: Wed Sep 12, 2007 4:50 pm
by mrkite
I'd use PEAR:MDB2 and prepared statements.

Code: Select all

$sth=$db->prepare("INSERT INTO dirlist (dept, title, fname, lname, phone, fax, email) VALUES (?,?,?,?,?,?,?)");
$sth->execute(array($dept,$title,$fname,$lname,$phone,$fax,$email));
$sth->free();
If you must use the old adapters, use mysql_real_escape_string() don't regex yourself.

edit:

Oh, I see what you're asking. You already have the queries in a file or something.

I'd match on \(.*?\), split on comma, strip out the outer ' and escape it.

edit2:

Code: Select all

function fixquery($string)
{
  preg_match('{(^.+VALUES\s+)\((.+?)\)(.*)$}',$string,$matches);
  $start=$matches[1];
  $end=$matches[3];

  $items=split(',',$matches[2]);
  foreach ($items as $i=>$v)
  {
        $items[$i]="'".mysql_real_escape_string(preg_replace("{^'(.*)'$}",'\1',$v))."'";
  }
  return $start.'('.join(',',$items).')'.$end;
}

Posted: Wed Sep 12, 2007 5:00 pm
by RobertGonzalez
Something along the lines of:

Code: Select all

[\w](')[\w]
perhaps. Or maybe

Code: Select all

(?<=\w)(')(?=\w)
These are somewhat tested and provided some usable result.

Re: need to replace single quotes in a query string with \'

Posted: Thu Sep 13, 2007 5:08 am
by stereofrog
Burrito wrote:I have a bunch of query strings that I need to replace single quotes in the values with escaped quotes:
Theoretically you can't, consider for example

Code: Select all

...VALUES(' 'apples','oranges' )
should it be (' \'apples','oranges' ) or (' \'apples\',\'oranges' ).

Practically, I think GeertDD's expression would work in most cases.

Posted: Thu Sep 13, 2007 11:43 am
by RobertGonzalez
Looking at GeertD pattern, it looks a bit better than mine.

Posted: Mon Sep 24, 2007 10:34 am
by Schatten
Why don't you use addcslashes($string,'\'') ?

Posted: Mon Sep 24, 2007 11:33 am
by RobertGonzalez
In this case I think the original poster has generated code he is working with. He is not writing the queries himself.