PHP转义存储过程参数

Question

I have litle problem with stored procedure when assign parameter value. Am assigning value as a date '2016-05-01','2016-07-30'

Procedure work fine when i manual enter date like this:

CALL calculateSeasonPrice(3, '2016-05-01','2016-07-30')

success return array with data.

But problem is when i assign variables as arguments

$carId = 3;
$sDate = '2016-05-01';
// and
$eDate = '2016-07-30';

$price = $db->query("CALL calculateSeasonPrice({$carID},{$sDate},{$eDate})"); // problem

return empty array

Does i need to escape this two variales. I also try this and not work!

$price = $db->query("CALL calculateSeasonPrice(".$carID.",".$sDate.",".$eDate.")"); // This work

What i miss here?

Answer 1

In the queries where you try to use variables you're forgetting the quotes for the values:

$price = $db->query("CALL calculateSeasonPrice('$carID','$sDate','$eDate')");

---

If you used prepared statements you would not have these kinds of problems. You can learn more about prepared statements for PDO and MySQLi.

PDO Example:

$carId = 3;
$sDate = '2016-05-01';
$eDate = '2016-07-30';

$price = "CALL calculateSeasonPrice(?,?,?)";
$queryResults = $dbh->prepare($price);
$queryResults->execute(array($carId, $sDate, $eDate));

MySQLi Example:

$price = "CALL calculateSeasonPrice(?,?,?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("iss", $carId, $sDate, $eDate);
$stmt->execute();

Fundamentally these are not very different, but there are some subtle elements that you'll want to get used to, depending on the API you choose for your work.