0

{ When i execute this query it return empty result but result is save in data base with enter code herefirst name is D\'Ami

SELECT u.UserFirstName, u.UserLastName, u.UserHasLoginAccess, u.UserEmail
FROM Users u
WHERE u.UserLicenseID =  '1603'
AND TRIM( u.UserEmail ) !=  ''
AND (
u.UserEmail LIKE  "%D\'Ami%"
OR u.UserFirstName LIKE  "%D\'Ami%"
OR u.UserLastName LIKE  "%D\'Ami%"
OR CONCAT_WS(  ' ', u.UserFirstName, u.UserLastName ) LIKE  "%D\'Ami%"
)
LIMIT 0 , 50}
Bharat Jain
  • 1

1 Answers1

0

when you want to search for single quotes using query, you must escape it with another single quote and not \. eg

u.UserEmail LIKE  "%D''Ami%"
OR u.UserFirstName LIKE  "%D''Ami%"
OR u.UserLastName LIKE  "%D''Ami%"
OR CONCAT_WS(  ' ', u.UserFirstName, u.UserLastName ) LIKE  "%D''Ami%"

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Thank you for reply actual in data base first name is save in this format D\'Ami but when i am using in like query it is not returning any result my question is that how write query that return result with name D\'Ami – Bharat Jain Feb 28 '13 at 09:05