query with slashes

Question

I have item name like girl\'s bag\'s. so query becomes like below

SELECT * 
FROM tbl_product 
WHERE itm_name LIKE '%girl\'s bag\'s%' 
    AND status = '1' 
    AND is_available = '1' 
ORDER BY itm_id DESC 
LIMIT 20

I have item with name girl\'s bag\'s. but i am not getting any result. Can anyone help , how should i format my item name to match with item in databse.

Answer 1

You need to escape quotes by doing two single quotes (so change \' to ''):

select * from tbl_product where itm_name like '%girl''s bag''s%' and status='1' and is_available='1' order by itm_id DESC limit 20.

But it would be better to use prepared staments and bind parameters to the sql, not concatenating strings to create the query.

EDIT: Based on the comment, the OP has \' literally in the data, so the query should use \'':

select * from tbl_product where itm_name like '%girl\''s bag\''s%' and status='1' and is_available='1' order by itm_id DESC limit 20.

Answer 2

Using bind_param and prepare statement as

$like = "%girl's bag's%";// pass  string
$sql = "select * from tbl_product where itm_name like ? and status= ? and is_available=? order by itm_id DESC limit ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('siii', $like, 1, 1, 20);
$stmt->execute();

Answer 3

If your actual value in your actual database is "girl\'s bag\'s", then something is fundamentally wrong with your data handling! You should not have escaped values stored in your database. Fix that first and foremost!

Having said that, if you want to match the value girl\'s bag\'s in an SQL query, the query needs to look like this:

WHERE ... LIKE '%girl\\\'s bag\\\'s%'

You want one literal backslash, which you need to escape with a backslash, followed by a backslash which escapes the single quote. So the first two \\ mean "\", and the third backslash just preserves the SQL quote syntax.

You never worry about the number of slashes manually, instead you use the proper database API to prepare your values correctly! That means you should be using prepared statements:

$value = "girl\'s bag\'s";

$stmt = $pdo->prepare('... WHERE ... LIKE ?');
$stmt->bindValue(1, '%' . $value . '%');
$stmt->execute();

How to do this exactly depends on your database API.

See The Great Escapism (Or: What You Need To Know To Work With Text Within Text).