MySQL warning error when user searches for '\' 1

[barryna]

I have a form where a user can do a search. If the user types in '\' (slash character), I get the following error below. What is the problem here?

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /var/

http://www/vhosts/httpdocs/ajax/do_search_users.php

on line 23

The full SQL related to this error is:
SELECT Users.`Index`, Users.ProfileName, Users.PrimaryEmail, Users.FirstName, Users.LastName FROM Users WHERE Users.ProfileName LIKE '%\%' OR Users.FirstName LIKE '\%' OR Users.LastName LIKE '\%' OR Users.PrimaryEmail = '\' ORDER BY Users.ProfileName, Users.LastName, Users.FirstName, Users.PrimaryEmail LIMIT 10;

Alternatively, I have a different search form where I can search for '\' and not get an error. This non-erroring SQL is below. Why does this SQL statement not error?

SELECT Movies.`Index`, Movies.Title FROM Movies WHERE Movies.Title LIKE '\%' UNION SELECT Movies.`Index`, Movies.Title FROM Movies WHERE Movies.Title LIKE '% \%' LIMIT 10;

How does someone normally handle this when building an SQL statement?

Nick

 

[vacunita]

a "\" is a special character that can mean various things, to MYSQL as well as to PHP (i assume that's what you are using)., when used in a query it can throw it off.

In any case running the search string through mysql_real_escape_string() function from PHP should help with that.

mysql_real_escape_string


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[barryna]

That is awesome, fixes the problem. I did not know that those were special characters when using php/mysql. I'm a little knew to php (but have accomplished a lot cause it's not much different from asp work I've done for many years). Things like this, which are php quarks, I'll learn as I go I guess. Do you recommend using mysql_real_escape_string() for any input recieved from a user that will go into a mysql query (including login pages, form submission that gets stored in the mysql db, etc)?

Nick

 

[vacunita]

Yes. you should never blindingly trust the user's input and always check the submissions. using mysql_real_escape_string helps to remove any possible inmvalid or malicious characters from the string.

Also have a read here, about SQL injection:

SQL Security

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.