0

Is there some way that I can remove an apostrophe from the middle of a string in PHP, but not at the beginning or end?

For example, I have a string like

'the cat jumped over the dog's lazy body'

The beginning and end quotes are in the string and are important, as they delineate the field in a record coming in from MySQLDump (created by BullZip, if that matters).

So in context, the record is similar to

INSERT INTO `SOMETABLE` () VALUES (1, '32295', NULL, 'RII20109', 'the cat jumped over the dog's lazy body', '11');

I need a way to escape (or remove) just the quote in the middle of the text, not at the beginning or end. I've tried all manner of str_replace options but feel I need a regex guru to help out, as I can't target the quote I want to change.

The overall process I use is to read the incoming file and chop it up with

$queries = explode(";", str_replace("\n\r", '', $sql));

and then execute each line with a MySQL query. That intermediate apostrophe is screwing things up.

Update

Sorry - in hindsight my question really wasn't very clear and has lead a few folk down the wrong path. The string I am working with is actually the whole line:

INSERT INTO `SOMETABLE` () VALUES (1, '32295', NULL, 'RII20109', 'the cat jumped over the dog's lazy body', '11');

not just a piece of it, as I misguidedly alluded to at the beginning. My bad. So as sin has suggested, there is really no way of knowing which apostrophe I should be working on, unless I pull the line apart even further (grab the VALUES () section somehow and then explode the contents and then use the techniques suggested on the individual fields. OMG! Don't think I can come at that!

BullZip is an MS Access to MySQL export program and yes, I believe that it should not be giving me this grief. It should be escaping the inter-field apostrophes, but there appears no setting to have it do so.

Another Update

To my significant embarrassment, after some more testing I have discovered that BullZip is handing quotes as it should, escaping with a slash. The offending character, upon closer scrutiny is not an apostrophe but one of those nasty MS Word "special" quotes (’), which of course I can fix with all the usual techniques. Sorry for wasting everybody’s time :(

Dave Spencer
  • 495
  • 1
  • 4
  • 15
  • To match `'` in the the middle you can use: [`(?<=\w)'(?=\w)`](https://regex101.com/r/2vMd62/1) – anubhava Jul 27 '17 at 18:57
  • *"That intermediate apostrophe is screwing things up."* - that's because it's considered as an sql injection. – Funk Forty Niner Jul 27 '17 at 18:57
  • https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – AbraCadaver Jul 27 '17 at 18:59
  • You should use an SQL Builder library instead of trying to sterilize the query yourself. Maybe look at this [question/answer](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) (related) – Tezra Jul 27 '17 at 18:59
  • 1
    wait, I feel I misunderstood the question. @anubhava 's suggestion seems to be what you want here. – Funk Forty Niner Jul 27 '17 at 19:00
  • If you're just worried about quoting on inserts, then look into prepared statements and parameter binding. Here's the docs for [PDO](https://secure.php.net/manual/en/pdo.prepared-statements.php) and [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). With parameter binding, you'll never have to worry about quoting again. – aynber Jul 27 '17 at 19:00
  • What is "BullZip"? And why does it generate invalid dumps? – Paul Spiegel Jul 27 '17 at 19:07
  • 2
    You can't find quotes like this and get any meaning out of it. You have to know how they relate to the rest of the _record_. Unless it's done that way, you don't know what's going on. `INSERT INTO SOMETABLE () VALUES (1, '32295', NULL, 'RII20109', 'the cat jumped over the dog,'s lazy body', '11');` –  Jul 27 '17 at 19:40
  • Dave, I had to leave for a while and revisited your question to see if there was any more activity, and it's unclear for me in regards as to how you (can/are) assigning that string. I believe that `$string = "'the cat jumped over the dog's lazy body'"; echo $newstring = stripslashes($string);` will in fact solve it and output `'the cat jumped over the dog's lazy body'` if you are at liberty to do that. Ping me back with @ followed by my name and your message to let me know. Or, to give me/us more information as to how this string is/can be assigned. – Funk Forty Niner Jul 27 '17 at 20:28
  • Note that a string value might also contain `;`, which you are using to parse the file. – Paul Spiegel Jul 27 '17 at 21:30
  • Good point about the `;` I hoped to get around that by using `";\n\r"` or `"\x3B0D0A"` to only get instances at the end of the line but for some reason `explode` is not finding that to split on, despite the hex editor saying that's what is there. – Dave Spencer Jul 27 '17 at 23:18
  • 1
    This seemed to work, however `$queries = preg_split("/(;\r\n|;\n\r|;\n|;\r)/", $sql);`. – Dave Spencer Jul 27 '17 at 23:25
  • 1
    Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – mickmackusa Jul 28 '17 at 03:38

3 Answers3

1

If you have a string containing single quotes like this:

$string = "'the dog's lazy body'";

why not remove all single quotes, and then put surrounding ones back in?

$new = "'" + str_replace("'","",$string) + "'";
RockwoodON
  • 149
  • 3
0

(You should definitely use RockwoodON's answer)

You could use a lookbehind and lookahead to make sure you are not at 1 end of the string.

(?<=.)'(?=.)

This checks that there is a character on both sides of the quote.

After question edit 1:

What you are asking for is not possible, someone could SQL inject and their text would be indistinguishable from code. Say the text was for instance:

Hello', '11'); DROP TABLE 'SOMETABLE'; -- har har

You would assume the real code was just a comment.

After question edit 2:

ok.

Funny Geeks
  • 483
  • 5
  • 12
-1

Escape strings when you put them in. The will come back out with apostrophes, backslashes, and double-quotes exactly as they existed before escaping.

Why on earth do you want to remove valid punctuation?

Rick James
  • 135,179
  • 13
  • 127
  • 222