I've got a simple PHP script that pull a row from a MySQL database based on a URL GET parameter, just an int called, say, ID.
If the GET parameter were to be an invalid ID for the database, such as someone enters an int that's not a row in the DB, or they try and enter a character or something, I'd like to display a default record.
The following code actually works, but I feel that it's bad code. I'm just not sure why, or what I should do. I know I should validate ID is an int before I run the database queries, but that doesn't solve what to do when an integer that is an invalid ID is used.
So to make this a proper question, how should I recover from mysqli_stmt_fetch returning false?
Go easy, I know I'm a nub :-)
$ID = (int)$_GET["id"];
$query = "SELECT `col1`, `col2`, `col3` FROM `table` WHERE `ID` = ?";
if ($stmt = mysqli_prepare($connection, $query))
{
mysqli_stmt_bind_param($stmt, "i", $ID);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $var1, $var2, $var3);
if (!mysqli_stmt_fetch($stmt))
{
$ID = 1; //A record I know exists and will always exist
mysqli_stmt_bind_param($stmt, "i", $ID);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $var1, $var2, $var3);
mysqli_stmt_fetch($stmt);
}
mysqli_stmt_close($stmt);
}
OK, so with some feedback and experimentation I've cast the ID as an int in PHP and changed the SQL query to this:
SELECT `col1`, `col2`, `col3`
FROM `table`
WHERE `ID` = ?
UNION
SELECT `col1`, `col2`, `col3`
FROM `table`
WHERE `ID` = 1
LIMIT 1
This works for all cases I can think of and seems to be secure. Any problems? Any better than what I had?
First check incoming $ID for char and so and set it to value that never be as ID in your table, then use query
SELECT col1, col2, col3 FROM table WHERE ID = :id
union all
SELECT col1, col2, col3 FROM table WHERE ID = 1 /* may :defaut_id if */
where not exist (select 1 from table WHERE ID = :id)