如何在SQL中使用单引号检查名称？

Question

I have used below code for checking lastname(case sensitive) from DB.

SELECT * FROM BL12_anncurtis_existing_customers WHERE BINARY LastName LIKE 'ravi'

Its work fine. But, it does not work when the name like below, I passes this last name using php variable($lname).

SELECT * FROM $table_name WHERE BINARY LastName LIKE '$last_name'
SELECT * FROM BL12_anncurtis_existing_customers WHERE BINARY LastName LIKE 'O'Connor'

How can I resolve this?.

Answer 1

When you're building the query in your PHP script, use the addslashes function on the value you are searching for:

$query = "SELECT * FROM BL12_anncurtis_existing_customers WHERE BINARY LastName LIKE '" . addslashes($name) . "'"

This will escape any characters that need it and will produce the result you are looking for.

Another solution is to use double quotes in the query instead of single quote:

SELECT * FROM BL12_anncurtis_existing_customers WHERE BINARY LastName LIKE "O'Connor"

but I think I prefer the first solution