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
– Kickstart, Commented Nov 8, 2013 at 23:34
Are you supposed to get the 2 variables one from POST and one from SESSION? — Mihai
– Mihai, Commented Nov 8, 2013 at 23:38
Even with quotes it won't work. The variables are substituted correctly. — user2970781
– user2970781, Commented Nov 8, 2013 at 23:43
what do you mean with "won't work"? do you have some errors? — arieljuod
– arieljuod, Commented Nov 8, 2013 at 23:44

Community · Accepted Answer · 2017-05-23 12:12:40Z

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, 2017 at 12:12

CommunityBot

11 silver badge

answered Nov 8, 2013 at 23:37

speccode

1,5629 silver badges11 bronze badges

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

12 Comments

user2970781 Over a year ago

Even when putting $searchcolor and $searchprice before the preparation it won't work. Thanks for the link :)

speccode Over a year ago

Did you try that query manually? There are any results? There is any error maybe?

Mike Brant Over a year ago

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.

user2970781 Over a year ago

Jep, I do have an active connection. When I substitute $searchprice by 1500 in the query string every works just as pleased.

speccode Over a year ago

What var_dump($_POST['price']) will show you? Stupid question but - are you 100% sure that you're connecting to good database?

|

Collectives™ on Stack Overflow

Strange behavior of mysql query string in PHP

1 Answer 1

12 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Linked

Related