php search function failing with single quote entry

Question

Ok, I have been on this for 1 hour now.. I am missing something very simple ? I need a new set of eyes on this ? I searched here and found a few things and tried to implement , but still came up short .

I have magic quotes turned off:

My search is for Sam's Club

In the database it is entered as : Sam's Club as well

Simple search function:

$q = htmlspecialchars($q);
// changes characters used in html to their equivalents, for example: < to &gt;
$q = mysql_real_escape_string($q);
// makes sure nobody uses SQL injection

$raw_results = mysql_query("SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE()
AND (`Title` LIKE '%".$q."%')") or die(mysql_error());

Still coming up empty result?

Obviously if I search for Sam it will query a result?

Here are two images from my phpMyAdmin:

enter image description here

SOLVED

I found the problem. I was including another list of functions in my header that I forgot about. That was messing with the variable.

Thanks for the troubleshooting! I still learned a ton!!

You need `htmlspecialchars` when *outputting* to a web page, not when you search in a database. And you need to escape `%` and `_` with `addcslashes` when doing a LIKE search. — Marcel Korpel, May 02 '13 at 19:15
Try running that query straight from phpmyadmin or whatever mysql client you are using to see the error that is thrown. — Dropzilla, May 02 '13 at 19:18
Exactly, that's why you should get rid of `htmlspecialchars`, like Ryan said. — Marcel Korpel, May 02 '13 at 19:18
You're searching for Sam's and not for Sam's which was the intention. (because you're using htmlspecialchars) — bestprogrammerintheworld, May 02 '13 at 19:19
Strange ? When I search in phpMyAdmin I get the results, and the query gets converted to this: WHERE `Title` LIKE 'Sams''s' — eberswine, May 02 '13 at 19:19
Move the `this.TypeID = this2.TypeID` from your `WHERE` clause, and turn it into a `JOIN` (just a friendly suggestion): `[LEFT] JOIN this on this2 ON this.TypeID = this2.TypeID`. Also `SELECT *`?, just don't do that please — Elias Van Ootegem, May 02 '13 at 19:24
Yes, but still no results, however it is echo Sam's Club now — eberswine, May 02 '13 at 19:24
@MarcelKorpel Never use `addslashes`. Please do not tell people to do this. — tadman, May 02 '13 at 19:25
How does the actual row you're searching in (in the db) look like? — bestprogrammerintheworld, May 02 '13 at 19:27
You uploaded two images with `Sams's Club`, which is different from `Sam's Club`. — Marcel Korpel, May 02 '13 at 19:41
yeah, i realized that, i was just adding it to show you what phpMyAdmin does ? It does come up with the result ( when I spell it right ) — eberswine, May 02 '13 at 19:43

Get Off My Lawn · Answer 1 · 2013-05-02T20:02:04.737

3

I would suggest getting rid of htmlspecialchars() it is converting your quote to ' or even '.

So when it tries to search the database it is using Sam's Club or Sam's Club as your search, but it won't be there because it is saved as: Sam's Club.

$q = mysql_real_escape_string($q);
// makes sure nobody uses SQL injection

$raw_results = mysql_query("SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE()
AND (`Title` LIKE '%".$q."%')") or die(mysql_error());

Please run this and reply with the string that is generated.

$q = mysql_real_escape_string($q);
echo "SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE() AND (`Title` LIKE '%".$q."%')";

edited May 02 '13 at 20:02

answered May 02 '13 at 19:16

Get Off My Lawn

34,175
38
176
338

Ok , got rid of htmlspecialchars(), but still no dice ? Do I need to write a small function to replace characters before running the query ? – eberswine May 02 '13 at 19:23
Can you `echo` the query string just before you execute it and paste it here so it's more clear what you're actually executing? – tadman May 02 '13 at 19:24
It is echo Sam's Club before and after now, but still no dice. I do have a $q = (isset($_GET['q']) ? $_GET['q'] : ""); before all this code too ? – eberswine May 02 '13 at 19:28
1

please echo out your full query. `echo "SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE() AND (\`Title\` LIKE '%".$q."%')"` – Get Off My Lawn May 02 '13 at 19:32
I did echo $raw_results and came up with this: Resource id #13 – eberswine May 02 '13 at 19:37
I added two images from my database – eberswine May 02 '13 at 19:41
you're searching for `Sam"s Club` and not `Sam's Club` (note that one is a quote and one is an apostrophe). – Get Off My Lawn May 02 '13 at 19:43
Or those could be two apostrophes. – Marcel Korpel May 02 '13 at 19:45
That is the strange thing in phpMyAdmin , I search for it with a single quote and then it returns the results page and it turns the ' into '' ? ( see above in purple and green image ) – eberswine May 02 '13 at 19:48
When I meant echo out the query, I didn't mean the result of mysql_query. Please look at my update. – Get Off My Lawn May 02 '13 at 19:56
SQL STRING=*SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE() AND Title LIKE '%Sam's%'* – eberswine May 02 '13 at 19:59

bestprogrammerintheworld · Answer 2 · 2013-05-03T06:00:51.813

1

Try this code:

   echo 'value q before=*' . $q . '*';

   $q = mysql_real_escape_string($q);
   echo 'value q after=*' . $q . '*';
   $q2 = mysql_escape_string($q);
   echo 'value q2 after=*' . $q2 . '*';

    // makes sure nobody uses SQL injection

    $sql = "SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE() AND `Title` LIKE '%".$q."%'";
    echo 'SQL STRING=*'.$sql.'*';
    $raw_results = mysql_query($sql) or die(mysql_error());
    echo 'RAW RESULTS=' . print_r($raw_results, true);

edited May 03 '13 at 06:00

answered May 02 '13 at 19:29

bestprogrammerintheworld

5,417
7
43
72

With an equal sign you can't use wildcards like `%`. – Marcel Korpel May 02 '13 at 19:35
@MarcelKorpel - I know that but it seems like OP's question is about having it equal. The db-row is Sam's Club and the search is Sam's Club. I don't think LIKE %Sam's Club%' would give any result if a row in the db is Sam's Club. Or am I wrong? – bestprogrammerintheworld May 02 '13 at 19:37
`%` matches 0 or more characters. – Marcel Korpel May 02 '13 at 19:40
Added 2 images from my phpMyAdmin above – eberswine May 02 '13 at 19:40
@eberswine - What's result of sql string? (edited my answer above) – bestprogrammerintheworld May 02 '13 at 19:42
@MarcelKorpel - Ok thanks about the information about the percentage sign in sql! :-) – bestprogrammerintheworld May 02 '13 at 19:46
SQL STRING=*Resource id #13* Query was empty – eberswine May 02 '13 at 19:49
@eberswine: No, not the resource, the query string. What is `$sql` after you insert `$q`? – Marcel Korpel May 02 '13 at 19:53
@eberswine Do you really have a table called 'this' ? – bestprogrammerintheworld May 02 '13 at 19:56
ok here it is: SQL STRING=*SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE() AND `Title` LIKE '%Sam's%'* – eberswine May 02 '13 at 19:58
No table called this, just replacing the real table name! – eberswine May 02 '13 at 19:58
If that's what you're getting you have not correctly escaped that variable and your statement has an injection bug that's causing it to fail. – tadman May 02 '13 at 19:59
@eberswine Strange, `$q` should be `Sam\'s` after using `mysql_real_escape_string`. Another reason to switch to prepared statements. And why do you replace the table name? That's a source of bugs... – Marcel Korpel May 02 '13 at 19:59
@eberswine - OK about this. If you copy that SELECT * FROM this, this2 WHERE this.TypeID = this2.TypeID AND this.status = 'Active' AND this.endDate >= CURDATE() AND Title LIKE '%Sam's%' TO the phpMyAdmin you get different results? – bestprogrammerintheworld May 02 '13 at 20:00
no, just on here, not in the script – eberswine May 02 '13 at 20:00
I get this in phpMyAdmin if I run the SQL #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's%'' at line 1 – eberswine May 02 '13 at 20:02
1

Both `'%Sam\'s%'` and `'%Sam''s%'` are valid from an escaping perspective. You need to have one of these or it won't work. You are missing the `mysql_real_escape_string` call. – tadman May 02 '13 at 20:03
2

None of this would be an issue if you were using PDO and [SQL placeholders](http://bobby-tables.com/php) because this is done for you automatically if you use them correctly. In this case, "You have an error in your SQL syntax" should be translated to "you have a giant SQL injection hole in your application." – tadman May 02 '13 at 20:04
@eberswine - What's the value q before? (see my editied answer) – bestprogrammerintheworld May 02 '13 at 20:06
value q before=*Sam's* – eberswine May 02 '13 at 20:08
Strange. I've edited my answer again. What results do you get of value q after and value q2 after ? – bestprogrammerintheworld May 03 '13 at 06:01

php search function failing with single quote entry

2 Answers2