日期函数的MySQL语法错误[关闭]

Question

I am writing a query which takes a pair of dates from user and searches whether it overlaps with any of the start and end column dates in my database table.

$from = $_GET['from'];
$to = $_GET['to'];
$sql="select bikeid from bikebookings where date('Y-m-d',strtotime(str_replace('/', '-', $from))) <= end and date('Y-m-d',strtotime(str_replace('/', '-', $to))) >= start";
mysqli_query($link,$sql) or die(mysqli_error($link));

I am getting an error as follows:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'strtotime(str_replace('/', '-', 20/03/2018))) <= end and date('Y-m-d',strtotime(' at line 1

Answer 1

You should use use mysql str_to_date function and for avoid sql injection. You should use binding param. For example:

$stmt = $mysqli->prepare("select bikeid from bikebookings 
     where str_to_date( ?, '%Y-%m-%d') <= end and  str_to_date( ?, '%Y-%m-%d') >= start");
$stmt->bind_param('ss',$from, $to);
$stmt->execute();