Escaping characters without using MySql functions in PHP

Question

This question is about a clarification. I have a varchar-like table field in a MySql database which may contain single quotes in its value.

I need to perform some queries similar to:

select my_field
from my_table
where my_field = 'xxx'rrr';

Of course, the above does not work in MySql Workbench.

I have tried:

select my_field
from my_table
where my_field = 'xxx''rrr';

and

select my_field
from my_table
where my_field = 'xxx\'rrr';

They both seem to work from MySql Workbench.

However, these queries are crafted and executed in a PHP application. I would like to know whether there is a possible caveat or whether I can use any of the methods above? What is the right way to escape single quotes without using MySql functions?

isn't the right way not to escape but use parameters instead? however, when inserting data you are actively aware of, as in the MySql Workbench, i'd say the right way is whichever way works. — Franz Gleichmann
– Franz Gleichmann, Commented Nov 17, 2016 at 8:21

cili · Accepted Answer · 2016-11-17 08:51:41Z

2

Escaping with backslash works. This is an example from one of my projects:

select * 
from articles
where title like '%O\'Neill%'

Maybe you should use like just in case you have some spaces you can't see.

EDIT: escaping with backslash works, it's not the right way.

edited Nov 17, 2016 at 8:51

answered Nov 17, 2016 at 8:24

cili

3174 silver badges15 bronze badges

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

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

There are several ways you could escape characters. Both of your suggestions are correct. Here's what the official documentation says:

There are several ways to include quote characters within a string:

A ' inside a string quoted with ' may be written as ''.

A " inside a string quoted with " may be written as "".

Precede the quote character by an escape character ().

A ' inside a string quoted with " needs no special treatment and need not be doubled or escaped. In the same way, " inside a string quoted with ' needs no special treatment.

To answer your question, there is no correct or incorrect way of escaping characters in a query nor any caveats - as long as you follow one of the above approaches.

For creating queries in php however, you don't need to do any of the above; the code handles this for you. Here's an example using PDO:

$some_variable = "'a'string'with'single-quotes'";
$query = "SELECT my_row FROM my_table WHERE some_column = :some_value";
$values = array(
  "some_value" => $some_variable,
);
$result = $db->get($query, $values)->fetch();

You could also use MySQLi should you prefer that.

Pixel1002 · Accepted Answer · 2016-11-17 08:26:32Z

1

Also, in PHP, you could you double quotes like this:

where my_field = "xxx'rrr";

answered Nov 17, 2016 at 8:26

Pixel1002

1961 gold badge3 silver badges12 bronze badges

Collectives™ on Stack Overflow

Escaping characters without using MySql functions in PHP

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related