Strange behavior of mysql query string in PHP

Question

I'm toying around with mysql and PHP and hit a VERY strange problem:

After establishing a successful database connection I set two variables for the query:

$searchcolor = $_SESSION["color"];
$searchprice = $_POST["price"];

$query = "SELECT `toys`.`id` FROM `database`.`toys` WHERE `toys`.`color` = $searchcolor AND `toys`.`price` = $searchprice;";
$result = mysqli_query($link, $query);

echo $query;

This querys won't work. When echoing it, it reads the correct string, like:

SELECT `toys`.`id` FROM `database`.`toys` WHERE `toys`.`color` = brown AND `toys`.`price` = 1500;

This code, however, works just fine:

$searchcolor = $_SESSION["color"];
$searchprice = $_POST["price"];

$query = "SELECT `toys`.`id` FROM `database`.`toys` WHERE `toys`.`color` = $searchcolor AND `toys`.`price` = 1500;";
$result = mysqli_query($link, $query);

echo $query;

First I though the $searchprice wasn't getting it's content by the $_POST array correctly. But the echoed search query in the first example seems to be fine.

It also works when setting $searchprice = 1500; instead of getting the $_POST-value.

I tried casting it to integer and stuff, but that didn't worked.

Cheers and thanks for every hint on this!

(The code is shortened!)

Table structure of toys:

id int(10)
name varchar(10)
color varchar(10)
price int(20)

Edit:

Woah, just made an interesting discovery:

echo "-".$searchprice."-";

Gives -5-

if ($searchprice == 5){echo "1";}
if ($searchprice == "5"){echo "2";}

Gives.. nothing?!

var_dump($searchprice);

Gives string(14) "5"

Edit:

echo bin2hex($searchprice);

Gives 3c6e6f62723e353c2f6e6f62723e (?!)

Solution: I used a unicode character in the submitting form. That broke everything. Lesson: Avoid unicode.

One thing that would stop those queries from working is that you have a string in the sql for the color which does not have quotes around it. — Kickstart, Nov 08 '13 at 23:34
Are you supposed to get the 2 variables one from POST and one from SESSION? — Mihai, Nov 08 '13 at 23:38
Even with quotes it won't work. The variables are substituted correctly. — user2970781, Nov 08 '13 at 23:43
what do you mean with "won't work"? do you have some errors? — arieljuod, Nov 08 '13 at 23:44
use single quotes once as $_POST['price'] and IF that doesn't works then use SESSION for accepting $searchprice value just to check ! — Shivanshu, Nov 08 '13 at 23:53
Single quotes won't work and storing the variable in SESSION didn't the trick either. But thanks for the hint =) — user2970781, Nov 09 '13 at 00:02

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

First of all you should read this: How can I prevent SQL injection in PHP?

Try this:

$q = mysqli_prepare($link, 'SELECT toys.id FROM toys WHERE toys.color = ? AND toys.price = ?');

mysqli_stmt_bind_param($q, 'si', $searchcolor, $searchprice); //d for double

$searchcolor = $_SESSION['color'];
$searchprice = $_POST['price'];

mysqli_stmt_execute($q);

Before that you should connect properly with DB. I see that you used database in FROM.

$link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');

edited May 23 '17 at 12:12

Community

1
1

answered Nov 08 '13 at 23:37

speccode

1,562
9
11

Even when putting $searchcolor and $searchprice before the preparation it won't work. Thanks for the link :) – user2970781 Nov 08 '13 at 23:40
Did you try that query manually? There are any results? There is any error maybe? – speccode Nov 08 '13 at 23:43
Are you getting a DB connection. This example shows bare bones for setting of parametrized prepared statements, you really hshould capture and handle errors in your real code. This includes when making DB connection. – Mike Brant Nov 08 '13 at 23:47
Jep, I do have an active connection. When I substitute $searchprice by 1500 in the query string every works just as pleased. – user2970781 Nov 08 '13 at 23:48
What `var_dump($_POST['price'])` will show you? Stupid question but - are you 100% sure that you're connecting to good database? – speccode Nov 09 '13 at 00:08
var dump gives a string. That should be normal since all POST-Variables are strings. Converting it to an integer doesn't seems to work.. mh. The database is good, im sure =) – user2970781 Nov 09 '13 at 00:19
Well... Try again my code. Should work without any change. If not working - only reason is that `$_SESSION['color']` AND `$_POST['price']` have values that not exists in table. – speccode Nov 09 '13 at 00:25
Adding info to the topic. – user2970781 Nov 09 '13 at 00:32
`trim($_POST['price'])`? You're sending something with that price. – speccode Nov 09 '13 at 00:40
Mhh, there don't seem to be any whitespaces. But have a look at the new edit. I almost freaked out. – user2970781 Nov 09 '13 at 00:42
UTF-8, BOM - I don't know. It's not a query fault so... Happy hunting :-) – speccode Nov 09 '13 at 00:48
Found it, I used Unicode in the form. That -somehow- broke it. Thanks for all your time, have a nice weekend =) – user2970781 Nov 09 '13 at 00:49

Strange behavior of mysql query string in PHP

1 Answers1