-1

I am trying to update some values on database with a simple query, but I could not get the query concated with date function to work.

$update_issued = "UPDATE books SET isIssued = 0 WHERE bookId =" . $_GET["bookid"]; //This one works
$update_date = "UPDATE loans SET returnDate = " . date("F j, Y, g:i a") . " WHERE bookId = " . $_GET["bookid"]; //This one does not
mysqli_query($db, $update_issued, $update_date);
Özenç B.
  • 928
  • 3
  • 8
  • 25
  • 1
    Please use prepared statements. – Alex Barker Nov 20 '19 at 21:00
  • 3
    The date must be enclosed into single quotes. But you would better [use prepared statements at all times](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) – GMB Nov 20 '19 at 21:00
  • What happens if `bookid` is "5); drop table books" – Luke Nov 20 '19 at 21:02
  • 1
    I know this is unsecure and could be easily exploited but I am trying to learn the basics before building something secure. This is basically a hobbyist project, won't be used by someone else – Özenç B. Nov 20 '19 at 21:04
  • @GMB I am afraid enclosing date function into single quotes does not work. It sends an empty string instead – Özenç B. Nov 20 '19 at 21:05
  • 1
    Prepared statements are "the basics." Don't make excuses to do things the wrong way. Also ensure your database columns are appropriately typed. A proper `DATETIME` column will not accept this date format. – miken32 Nov 20 '19 at 22:25
  • @miken32 You are right, I will learn them. However, the date column is a varchar, not datetime so it does accept the format. – Özenç B. Nov 21 '19 at 18:01

2 Answers2

1

In order to pass parameters to a query, use prepared statements in order to prevent SQL injection attacks.

$update_date = mysql_prepare($db, "UPDATE loans SET returnDate = ? WHERE bookId = ?");
mysqli_stmt_bind_param($update_date, 'si',  date("F j, Y, g:i a"), $_GET["bookid"]);
mysqli_stmt_execute($update_date);

By doing this, attackers are prevented from passing a string that could cause the system to perform an unwanted action; if an attacker passed 0 or 1=1 in ?bookid, your query would affect all rows.

As a minor side not, it's much nicer to use the object oriented style and call $db->prepare instead of mysqli_prepare($db...; and even better yet to use the PDO layer.

Tordek
  • 10,628
  • 3
  • 36
  • 67
-1

So, I figured what the problem was. returnDate column on the DB expects a varchar value, and the new value has to be enclosed in quotes just like strings. I tried enclosing it before asking the question but put the quotes in the wrong place.

It should look like this:

$update_date = "UPDATE loans SET returnDate = '" . date("F j, Y, g:i a") . "' WHERE bookId = " . $_GET["bookid"]; //This one does not

So the returned value from date function will be enclosed in single quotes just like you would do:

UPDATE loans SET returnDate = '20.11.2019 22:33` WHERE bookId = " . $_GET["bookid"];

Özenç B.
  • 928
  • 3
  • 8
  • 25