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

Any questions involving matching text strings to patterns - the pattern is called a "regular expression."

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

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

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
GeertDD
Forum Contributor
Posts: 274
Joined: Sun Oct 22, 2006 1:47 am
Location: Belgium

Post by GeertDD »

Replace all single quotes that are not preceded nor followed directly by parentheses or comma's.

Code: Select all

preg_replace('/(?<![(),])\'(?![(),])/', '\\\'', $str);
mrkite
Forum Contributor
Posts: 104
Joined: Tue Sep 11, 2007 4:19 am

Post 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;
}
Last edited by mrkite on Wed Sep 12, 2007 5:02 pm, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
stereofrog
Forum Contributor
Posts: 386
Joined: Mon Dec 04, 2006 6:10 am

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

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Looking at GeertD pattern, it looks a bit better than mine.
Schatten
Forum Newbie
Posts: 3
Joined: Mon Sep 24, 2007 10:23 am

Post by Schatten »

Why don't you use addcslashes($string,'\'') ?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

In this case I think the original poster has generated code he is working with. He is not writing the queries himself.
Post Reply