php - fetching associative array of pg_execute() prepared statement returns false

Question

Context

I'm trying to implement a (hopefully) simple login system using PHP and PostgreSQL.

This is the postgres table containing usernames and hashed passwords.

Users are not meant to be able to create new rows in this table so I already hashed the passwords using password_hash('password', PASSWORD_BCRYPT) and then manually copypasted the value in the table.

Let me know if you think this could pose a problem.

Users can, however, login to an already existing account by inputting the right username and password combination into a login form.

When the login button is pressed I need to retrieve information about the user entered, so that if it matches any user I can then verify the password using password_verify().

The Problem

When the login button is clicked I run this code:

$dbconn = pg_connect("host=host dbname=dbname user=user password=pwd");
if (!$dbconn) {
    die("Error in connection: " . pg_last_error());
}

// setting the username as it would be provided by the form field
$username = 'Dan';

// maybe unrelated: why do I need to write 'username' instead of username? all of my other queries work without ''
$query = "SELECT * FROM user WHERE 'username' = $1";

$stmt = pg_prepare($dbconn, "", $query);
var_dump($stmt); // gives output "resource(3) of type (pgsql result)", got no clue on how to see what's indside

$result = pg_execute($dbconn, "", array($username));
var_dump($result); // gives output "resource(4) of type (pgsql result)"

$row = pg_fetch_assoc($result);
var_dump($row); // gives output "bool(false)", indicating that pg_fetch_assoc() failed

The main problem would be that pg_fetch_assoc() fails and returns false, however I believe this may also be caused by an incorrect query or by the way I build the statement, hence why I included everything.

Edit

Forgot to mention that I also tried formulating the query as:

$query = "SELECT * FROM user WHERE username = $1";

And in this case I get and error saying: Warning: pg_prepare(): Query failed: ERROR: column "username" does not exist LINE 1: SELECT * FROM user WHERE username = $1.

`'username' = 'Dan'` is false, therefore the query returns no rows, pg_fetch_assoc has nothing to fetch and returns false. The query should be `"SELECT * FROM user WHERE username = $1"`. pg_fetch_assoc returning false does not necessarily mean failure but that there is nothing to fetch. — Stefanov.sm, Jul 15 '20 at 19:56
@Stefanov.sm thanks a lot for this, I forgot to mention that I first tried running the query like you said. However, in that case I got an error saying `Warning: pg_prepare(): Query failed: ERROR: column "username" does not exist LINE 1: SELECT * FROM user WHERE username = $1 ` — Dispensable Joe, Jul 15 '20 at 20:22
If a statement "fails", why not check for that? If there was an incorrect query, you would receive an obvious error - if you checked for it — Nico Haase, Jul 15 '20 at 20:23
@NicoHaase yes, in the webpage I check `if (!$stmt) {$location = '/path/login.php?error=sqlerror'; header('Location: ' . $location); exit();` But I need to understand why it fails — Dispensable Joe, Jul 15 '20 at 20:27
"user" is a reserved keyword in postgresql. See https://stackoverflow.com/questions/9010634/postgres-what-is-the-query-select-from-user-actually-doing — Don't Panic, Jul 15 '20 at 21:06
@Don'tPanic thanks a lot, I wasn't aware of this list and it instantly solved my issue! — Dispensable Joe, Jul 15 '20 at 23:04
@Stefanov.sm thanks, your answer works, I just prefer not using double quotes — Dispensable Joe, Jul 15 '20 at 23:19
`!$stmt` is not a proper error check, as you don't check the **cause** of the error. Additionally, that code isn't part of the snippet you've shared — Nico Haase, Jul 16 '20 at 05:38

score 0 · Accepted Answer · answered Jul 15 '20 at 23:16

Thanks to Don't Panic's comment I renamed my user table to utiliser and everything worked as it should have.

It should also be noted that this could be circumvented by using double quotes on the table name (according to this answer), however, double quotes are evil so better to just stay away.

Here's the full table of reserved words to avoid as table/column names.

php - fetching associative array of pg_execute() prepared statement returns false

Context

The Problem

Edit

1 Answers1