how to search a name that include apostrophe in SQL

Question

i want to search a title that include apostrophe in it. i call the title from variable.

for example:

$mytitle = "Daddy's Home";

then i try to search it.

$apostrophe = $val;
$replacementsapostrophe = [
"'" => "''",
];
$newval = strtr($apostrophe, $replacementsapostrophe);

$query2 = $db->prepare ("SELECT category, id_master_post, master_post_name FROM `master_post` WHERE master_post_name = '$newval'");
$query2->execute();
$value2 = $query2->fetch();

Error given

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Home 2'' at line 1 in C:\xampp\htdocs\piratefiles\list.php:31 Stack trace: #0 C:\xampp\htdocs\piratefiles\list.php(31): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\piratefiles\list.php on line 31

im already try '%''%' still not working.

How **specifically** are you trying to search it? Please include the supporting PHP code, not just your SQL statement itself; using prepared statements would solve this. — Obsidian Age, Mar 19 '18 at 01:58
If the `$mytitle` has a single quote in it, your query will fail. You should definitely be using prepared statements here. — Tim Biegeleisen, Mar 19 '18 at 01:59
*How can I prevent SQL injection in PHP?* = *How can I prevent ' breaking my querys in PHP?* — Lawrence Cherone, Mar 19 '18 at 02:04

SayoPaul · Answer 1 · 2018-03-19T02:15:04.427

0

You should use prepared statements to avoid sql injection but to answer your question . An example;

 try{
   $db=new PDO(DSN,USER,PASS);
 }catch(PDOException $e){
   echo "couldn't connect cos of $e";
}
$sqlQuery = "SELECT category, id_master_post, master_post_name FROM `master_post` WHERE master_post_name = ? "
$prepared= $db->prepare($sqlQuery);
$prepared->execute($myTitle);
$resultObject = $prepared->fetchObject() ;

edited Mar 19 '18 at 02:15

answered Mar 19 '18 at 01:55

SayoPaul

153
2
10

Your suggestion to use prepared statements is good, but your answer is still using a PHP variable directly inserted into the string query. – Tim Biegeleisen Mar 19 '18 at 01:59
"*Use prepared statements*" and "*just use the variable directly*" are mutually exclusive statements ;) – Obsidian Age Mar 19 '18 at 01:59
Yeah I just want to help him figure out what's wrong firstly. I'll update it to use prepared statements – SayoPaul Mar 19 '18 at 02:00
sorry about it, im already add prepare but im not write full of my code. im edit it, and im try replaace `'` to `''`. i want to know is that save to using `strtr` in sql? – jazuly aja Mar 19 '18 at 02:09
I just updated my answer to show you how it should work using prepared statements. You can try it – SayoPaul Mar 19 '18 at 02:11

how to search a name that include apostrophe in SQL

1 Answers1