SQL query containing IP address works as normal query but not as prepared statement

Question

see bottom for the code that worked

I have a page that checks the last time a user pressed a button from the user's current IP address. The code looks like this:

$lastPressQuery = "SELECT MAX(tstamp) AS 'last_press' FROM presses WHERE ip = $ipNum";
$lastPressResult = mysqli_query($conn, $lastPressQuery);
$lastPressRow = mysqli_fetch_assoc($lastPressResult);

($ipNum is converted using ip2long)

This code works as intended. However, in line with best practices, I would like to change this to a prepared statement. This is the code I wrote for the prepared statement:

$lastPressQuery = $conn -> prepare("SELECT MAX(tstamp) AS 'last_press' FROM presses WHERE ip = ?");
$lastPressQuery -> bind_param("i", $ipNum);
$lastPressResult = $lastPressQuery -> execute();
$lastPressRow = mysqli_fetch_assoc($lastPressResult);

This does not work. execute() returns false.

I have tried leaving the IP address as a string, leaving $ip as a string and putting apostrophes around it or the question mark, checking ipNum for mistakes, and entering the query it should create directly into SQL (which works). A later prepared statement to INSERT data works fine. What is causing this prepared statement to fail?

EDIT: I finally got this to work. The code that succeeded is:

$lastPressQuery = $conn -> prepare("SELECT MAX(tstamp) AS 'last_press' FROM presses WHERE ip = ?");
$lastPressQuery -> bind_param("i", $ipNum);
$lastPressQuery -> execute();
$lastPressResult = $lastPressQuery -> get_result();

you know IP != person. one person can use many IP's and one IP can be thousands of different people. — , Aug 04 '19 at 22:15
I'm aware, but until everything I have works, I'm not concerned with robustly distinguishing between people. — Tumblewood, Aug 05 '19 at 01:54
Make sure [mysqli raises exceptions](https://stackoverflow.com/a/22662582/1422451). What is `gettype($ipNum)`? — Parfait, Aug 05 '19 at 02:16
What exactly is `$ipNum`? Could you give an example? Why is it an integer? — Dharman, Aug 05 '19 at 11:53

score 1 · Answer 1 · answered Aug 04 '19 at 22:14

1

You are passing the prepared statement object, not the result, try

$lastPressResult = $lastPressResult -> execute();
$lastPressRow = mysqli_fetch_assoc($lastPressResult);

answered Aug 04 '19 at 22:14

Fran Cerezo

940
3
8
19

Thank you for informing me. I've updated my code to match, but `execute()` still returns false when I run this. – Tumblewood Aug 05 '19 at 01:56
Paste your CREATE TABLE statement, please, maybe the field is not wide enough or is unsigned. – Fran Cerezo Aug 05 '19 at 02:13
I'm not sure what you mean by "field is not wide enough or is unsigned" but I do know that the same query works with mysqli_query() and no preparation. EDIT: I thought earlier that execute was returning false, `echo $lastPressResult;` returns true (or rather, 1). – Tumblewood Aug 05 '19 at 03:28

score 0 · Accepted Answer · answered Aug 29 '19 at 06:39

The code that succeeded is:

$lastPressQuery = $conn -> prepare("SELECT MAX(tstamp) AS 'last_press' FROM presses WHERE ip = ?");
$lastPressQuery -> bind_param("i", $ipNum);
$lastPressQuery -> execute();
$lastPressResult = $lastPressQuery -> get_result();

SQL query containing IP address works as normal query but not as prepared statement

2 Answers2