MySQL SELECT statement using PHP GET variable

Question

I have a PHP script that is generating a MySQL select statement:

select * from words where word = 'Classic'

There is exactly one word in the words table with the variable word equal to Classic.

When my PHP page executes, I get no results from the query. If I echo the string that is being used to execute the query, cut and paste that into the SQL window in PHPMyAdmin in the database, I also get no results. However, if I re-type that EXACT string into the SQL window in PHPMyAdmin (with the same quote characters), I get the proper result of one row.

The word Classic from the select statement is gotten from a PHP GET (see code below). I can echo the $word variable, and get the correct result of 'Classic'. What am I doing wrong?

Here is my code:

<?php

  require ('dbconnect.php');

  $word = $_GET["word"];

  $selectStr = "SELECT * FROM words WHERE word = '" . $word . "'";

  if ($results = MySQL($dbName, $selectStr))
  {
    $rowCount = MySQL_NUMROWS($results);
  }

  $resultRow = MYSQL_FETCH_ROW($results);

  $wordID = $resultRow[0];

?>

you should NEVER use a request variable directly on a SQL statement. — samura
– samura, Commented Sep 5, 2012 at 17:06
It does not answer your question, but remember you must always sanitize user input before using it in a query to avoid SQL injection! — Marco
– Marco, Commented Sep 5, 2012 at 17:07
Matt, you might want to read "Essential PHP security" or something first. Quick and easy read that covers the basics. There's nothing stopping me from sending arbitrary data to your endpoint. — Claudiu
– Claudiu, Commented Sep 5, 2012 at 17:16
@Matt: there is no such thing as controlling get/post. That's user-provided data from the get go. It is *BEYOND trivial to manipulate GET data by just editing the url the browser is hitting, and POST can be forged with a simple client-side .html file that posts directly to your script. You are BEGGING to get your server hacked. — Marc B
– Marc B, Commented Sep 5, 2012 at 17:17

Claudiu · Accepted Answer · 2012-09-05 17:14:26Z

3

Please, please, please sanitize that word. mysql_real_escape_string() should do the trick.

$selectStr = "SELECT * FROM words WHERE word LIKE '" . $sanitized_word_i_promise . "'"; should work :)

Just to explain: "=" should work for exact matches. This includes uppercase / lowercase, spaces etc. You should probably trim that result first too, before using it in the query.

If you have foo stored in the database (note the space at the end) - it won't match foo, without a space. You'll want to use LIKE 'foo%' - probably.

Either way, Sourabh is right, although performance wise, this isn't a big hit when trying to match exact strings, you should look for the problem in other places first (such as, is the item in the database an exact match?).

edited Sep 5, 2012 at 17:14

answered Sep 5, 2012 at 17:07

Claudiu

3,2611 gold badge18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sourabh Over a year ago

using like is not a good option when he wants an exact match. performance will be bad

Matt Brand Over a year ago

OK. Understood. With the word sanitized, and using the LIKE statement, I got it to work properly. Thank you!

JClaspill Over a year ago

You might try trim on the word too and removing the LIKE statement. Could be simple whitespace that make the = operator not work and the LIKE work. Can use php trim() or mysql, depending on which server is less utilized.

BOMEz · Accepted Answer · 2012-09-05 17:10:02Z

2

First off you should not take any user input and directly input it into a query without sanitizing it, or using a prepared statement.

Now that we've gotten that out of the way: have you tried doing a strcmp() with the variable and your string written in? Such as

echo strcmp($_GET['word'], "Classic")

If you get a result other than 0 it means they are not the same, most likely there will be a whitespace of some sort in the $_GET variable. use trim() on it to take out whitespace. Also could be a case sensitivity issue as well.

answered Sep 5, 2012 at 17:10

BOMEz

1,01014 silver badges35 bronze badges

2 Comments

Matt Brand Over a year ago

I get a 1, which means the words are not the same. But I have tried using trim() and mysql_real_escape_string(), and the strings still do not match.

Claudiu Over a year ago

Probably some upper/lower case issue.

Collectives™ on Stack Overflow

MySQL SELECT statement using PHP GET variable

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related