This question is about a clarification. I have a
where my_field = 'xxx'rrr';
where my_field = 'xxx''rrr';
where my_field = 'xxx\'rrr';
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.