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:
perhaps. Or maybe
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
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.