Mattlinux1 Mattlinux1 - 6 months ago 16
SQL Question

How to convert a SQL query into a prepared statement PHP

I'm currently stuck in converting the below SQL Query into a prepared statement.

$XSS_BLOCK2 = "22-07-2004";
$XSS_BLOCK3 = "20-05-2016";
$dateswitch1 = date("Y-m-d", strtotime($XSS_BLOCK2));
$dateswitch2 = date("Y-m-d", strtotime($XSS_BLOCK3));

$securesqlstring = $secureconn->prepare("SELECT * FROM Lateday WHERE $dateswitch1 AND $dateswitch2 BETWEEN StartDate AND EndDate");


E.g. working code
$securesqlstring = $secureconn->prepare("SELECT * FROM Lateday WHERE '2004-07-22' AND '2016-05-20' BETWEEN StartDate AND EndDate");


Code Example:

$XSS_BLOCK2 = "22-07-2004";
$XSS_BLOCK3 = "20-05-2016";
$dateswitch1 = date("Y-m-d", strtotime($XSS_BLOCK2));
$dateswitch2 = date("Y-m-d", strtotime($XSS_BLOCK3));
$securesqlstring = $secureconn->prepare("SELECT * FROM Lateday WHERE ? AND ? BETWEEN StartDate AND EndDate");
$securesqlstring->bindParam(1,$dateswitch1);
$securesqlstring->bindParam(2,$dateswitch2);
$securesqlstring->execute();


Currently not working.

Example of working update statement that worked on another project I want to convert the SQL Query above to something like the example below:

$id = $_POST["id"];
$stocklevel = $_POST["stocklevel"];

$XSS_Block1 = htmlentities ($id, ENT_QUOTES, "UTF-8");
$XSS_Block2 = htmlentities ($stocklevel, ENT_QUOTES, "UTF-8");

$conn = new PDO("mysql:host=localhost;dbname=;","","");
$mattssqlstring = $conn->prepare("UPDATE `products` SET stocklevel=stocklevel-? WHERE ID=? and stocklevel = ?");
$mattssqlstring->bindParam(1,$XSS_Block2);
$mattssqlstring->bindParam(2,$XSS_Block1);
$mattssqlstring->bindParam(3,$XSS_Block2);
$mattssqlstring->execute();

Answer
$XSS_BLOCK2 = "22-07-2004";
$XSS_BLOCK3 = "20-05-2016";
$securesqlstring = $secureconn->prepare("SELECT * FROM `Lateday` WHERE STR_TO_DATE(:date1,'%d-%m-%Y') AND STR_TO_DATE(:date2,'%d-%m-%Y') BETWEEN `StartDate` AND `EndDate`");
$mattssqlstring->bindParam(':date1',$XSS_BLOCK2);
$mattssqlstring->bindParam(':date2',$XSS_BLOCK3);
$securesqlstring->execute();
Comments