Replacing String with Apostrophe in MySQL When running a find and replace query

Question

I have a table called my_table with 2 fields ID and dbtext.

The fields in the database are:

ID: 1 dbtext: Hi this is Bobby's Stuff

I am trying to find and replace text within the dbtext field. Here is my code:

$findtext = 'Hi this is Bobby\'s Stuff';
$replacetext = 'Hi this is Larry\'s Stuff';

$findtext = stripslashes($findtext);
$replacetext = stripslashes($replacetext);



  $sql = "UPDATE my_table SET dbtext = REPLACE(dbtext,'" . $findtext . "','" . $replacetext . "') WHERE ID = '" . $ID . "' ";
$wpdb->query($sql);

Unfortunately when I strip the slashes, the query won't run due to the single quotes breaking the query.

However, if I don't strip the slashes, the query wont find a match as there is no slash in the database.

How do I handle this and make sure it will work with single quotes and double quote situations?

If performance is not critical for this task, consider fetching the rows and updating via wpdb::update(). Calling entire string queries asks for trouble. — Robo Robok
– Robo Robok, Commented Aug 1, 2020 at 1:30

Chilarai · Accepted Answer · 2020-08-01 01:36:12Z

1

Mysql follows triple-slash pattern for extra security. Try the code below

$findtext = 'Hi this is Bobby\\\'s Stuff';
$replacetext = 'Hi this is Larry\\\'s Stuff';

  $sql = "UPDATE my_table SET dbtext = REPLACE(dbtext,'" . $findtext . "','" . $replacetext . "') WHERE ID = '" . $ID . "' ";
$wpdb->query($sql);

edited Aug 1, 2020 at 1:36

answered Aug 1, 2020 at 1:29

Chilarai

1,8882 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CRAIG Over a year ago

The triple slash worked when I added them manually. Trying to sort out how to add them dynamically though. Tried $findtext = preg_replace('(/\\\\/)','(/\\/)',$findtext); Plus other versions of this, but not having any luck.

Chilarai Over a year ago

Check this thread here stackoverflow.com/questions/63110568/…

Chilarai Over a year ago

@CRAIG You can also use this $e = str_replace("'", "\\\\\'",$findtext);

Collectives™ on Stack Overflow

Replacing String with Apostrophe in MySQL When running a find and replace query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related