How works php date BETWEEN

Question

I have a query and works:

$sql2 = "SELECT id FROM table WHERE '2022-06-06' BETWEEN date(se_from) AND date(se_to)";

But when the date is dynamic the query fails:

$pick_date = '2022-06-06';  
$sql2 = "SELECT id FROM tblseasons WHERE $pick_date BETWEEN date(se_from) AND date(se_to)";

Can't understand, can anyone explain.

Thanks

Hint: print the SQL string before executing and compare. TL;DR: use prepared statements and don’t craft SQL. — Boris the Spider, May 04 '22 at 20:16
You might need to escape the date, as in `SELECT id FROM tblseasons WHERE '$pick_date' BETWEEN date(se_from) AND date(se_to)";` — Guido Faecke, May 04 '22 at 20:16
Does this answer your question? [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — user3783243, May 04 '22 at 20:25
Your query currently would be `SELECT id FROM table WHERE 2022-06-06 BETWEEN date(se_from) AND date(se_to)`; which would be `2010` between the two dates. It is 2010 because `2022-06-06` is run as math and subtracts the month and day from the year. — user3783243, May 04 '22 at 20:27

Ken Lee · Answer 1 · 2022-05-04T22:30:42.483

Your query lacks parentheses in the date (so it fails to do what you want as the query will be invalid)

For security, please use parameterized prepared statement to avoid SQL injection attacks instead

For mysqli, it will be:

$conn = mysqli_connect("localhost", "user", "dbpass", "dbname1");

$pick_date = '2022-06-06';  

$sql2 = "SELECT id FROM tblseasons WHERE ? BETWEEN date(se_from) AND date(se_to)";

$stmt = $conn->prepare($sql2); 
$stmt->bind_param("s", $pick_date);
$stmt->execute();

$result = $stmt->get_result(); // get the mysqli result

while ($row = $result->fetch_assoc()) {
    echo $row['id'] . "<br>"; // if you want to see the result;
}

For PDO, it will be

$dbh = new PDO('mysql:host=localhost;dbname=dbname1', "user", "dbpass");

$pick_date = '2022-06-06';  

$string1 = "SELECT id FROM tblseasons WHERE :pick_date BETWEEN date(se_from) AND date(se_to)";

$stmt = $dbh->prepare($string1, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

$stmt->execute([':pick_date' => $pick_date]); 

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row["id"] . "<br>"; // if you want to see the result;
}

How works php date BETWEEN

1 Answers1