JVerstry JVerstry - 24 days ago 8
MySQL Question

Escaping characters without using MySql functions in PHP

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?

Answer

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->execute($query, $values);

You could also use MySQLi should you prefer that.