Correcting an UPDATE statement (and making it more secure!)

Question

I'm trying to a single value in my DB...When I run it through the console, it works correctly (as I'm replacing the variables with numbers and text).. However, My query is not returning a value for book ID when I insert the PHP variable for it.. It's because the book_id is unpopulated...

$query = "UPDATE books "
       . "SET readstatus='".$readstatus."' "
       . "WHERE book_id=".$book_id;
echo $query

The echoed query states: UPDATE books SET readstatus='half' WHERE book_id=0

The book ID is stored in the URI as bookstatusupdate.php?book_id=

Just cannot figure this one out!:(

Answer 1

It would help to know the error. Firstly, echo out the query:

$query = "UPDATE books "
       . "SET readstatus='".$readstatus."' "
       . "WHERE book_id=".$book_id;
echo $query;

I would guess that $book_id is unpopulated, so the query fails. What you should really be doing to make it secure is casting integers with (int) and wrapping strings in mysqli_real_escape_string().

$query = "UPDATE books "
        ."SET readstatus='". mysqli_real_escape_string( $readstatus )."' "
        ."WHERE book_id=". (int) $book_id;

If you're trying to get data from the URL, do it like so:

$book_id = (int) $_GET['book_id'];

$query = "UPDATE books "
        ."SET readstatus='". mysqli_real_escape_string( $readstatus )."' "
        ."WHERE book_id=". (int) $book_id;

echo $query;