0

I'm using PHP with MySQL to make a basic search engine on my website but the problem is that I'm using something for example:

SELECT * FROM shope_details WHERE shop_name =  'willy's Kitchen';

I want to search strings with apostrophe s in MySQL. I am unable to search in where condition and I don't want to use like operator in MySQL. I know this has been asked before, particularly for PHP, but I just don't know how I would implement this in a search query.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Tenacious Techno
  • 11
  • 3
  • 1
    You should use prepared statements. It handles the special characters and prevents SQL injection. – Fatemeh Gharri Nov 15 '22 at 08:02
  • by using variable can you explain? – Tenacious Techno Nov 15 '22 at 08:03
  • You tagged this with Laravel. If you are indeed using Laravel you should not be using raw SQL queries at all unless absolutely necessary – apokryfos Nov 15 '22 at 08:04
  • The single quote char can be quoted by double it. In general the char should be quoted with backslash: `.. WHERE shop_name = 'willy\'s Kitchen';`. Remember - if you call the query from a language then the backslash itself should be quoted in this language. – Akina Nov 15 '22 at 08:05
  • i m using eg: $searchStr .= "BINARY shope_details.shop_name = '".$searchText."' AND shope_details.show_hide = 0 OR "; – Tenacious Techno Nov 15 '22 at 08:05

1 Answers1

2

It's better to use prepared statements. It handles the special characters and prevents SQL injection. This is an example in PDO:

$shop_name = "willy's Kitchen"
$query = "SELECT * FROM shope_details WHERE shop_name = :shop_name";

$stm = $this->connection->prepare($query);
$stm->bindValue(':shop_name', $shop_name, PDO::PARAM_STR);

if ($stm->execute())
{
    $entries = $stm->fetchAll();
}
Fatemeh Gharri
  • 369
  • 2
  • 6
  • 20