PHP中mysql查询字符串的奇怪行为

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.

Answer 1

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');