CODEWITHSUNDEEP
mysqlsql
nehem
nehem

Reputation: 13672

How to escape single & double quotes in MySQL select?

select name from movies where id = 1; gives me

name
----------
How the 'A' Stole Christmas

How would I select in order to get 

name
----------
How the \'A\' Stole Christmas

I can use replace(title,'\'','\\\'') which turns ugly since I need to do it twice one for single & double quote, Curious to know if there is cleaner approach

Upvotes: 2

Views: 2444

Answers (1)

Peter Scott
Peter Scott

Reputation: 1316

Struggling with escaping characters is a sign that you may be unnecessarily converting strings manually as data flows through different systems. The question I would ask is whether it is necessary to have escaped strings for your requirements.

When constructing and executing your queries you should use bind variables which removes the need to quote strings to build up your Sql queries and minimizes the risk of Sql injection security issues.

See http://php.net/manual/en/mysqli-stmt.bind-param.php

Once you have Sql results in a variable or PHP structure it is often better to find functions/libraries such as JSON functions described at https://coderwall.com/p/p2kumg/json_encode-vs-serialize-with-php-arrays

And of course there's http://www.w3schools.com/php/func_string_addslashes.asp as mentioned by D4V1D as a comment.

Edit ... There also appears to be a Mysql QUOTE() function that does what you are asking.

See http://www.w3resource.com/mysql/string-functions/mysql-quote-function.php

Upvotes: 2

Related Questions