Thread: pg_query won't execute
I’m learning PHP for PostgreSQL and have come across something I can’t figure out.
The statement “SELECT COUNT(*) FROM users WHERE username = ‘john’ works just fine in the pgAdmin query tool, but not like this:
$sql = “SELECT COUNT(*) FROM users WHERE username = $username”;
If ($result = pg_query($dbconn,$sql)) {
…process statements…
} else {
echo “Could not execute $sql”;
}
$dbconn is a valid resource (I’ve changed up the $sql statement to test, and it works just fine), so no problem there. The problem is incurred when I attempt to COUNT something.
Is there a different method I should be using for returning a count?
Thanks
Dale
"Dale Schmitz" <dschmitz4@cox.net> writes: > The statement "SELECT COUNT(*) FROM users WHERE username = 'john' works just > fine in the pgAdmin query tool, but not like this: > $sql = "SELECT COUNT(*) FROM users WHERE username = $username"; What you're presumably ending up with is a query string like SELECT COUNT(*) FROM users WHERE username = john which isn't going to work ... unless there's a column named "john" in "users", and even then it probably doesn't produce the result you intended. You need to quote the inserted value as a literal. But really the better way would be to insert "john" as a parameter. If you do something like this: $sql = "SELECT COUNT(*) FROM users WHERE username = '$username'"; it'd appear to work, until you ran into a username containing a single quote. (You've heard of SQL injection, right?) I don't know PHP, so I'm not sure whether it provides any convenient way to produce a safely escaped literal equivalent of an arbitrary input string. But I'm almost sure it will let you do something along the lines of $sql = "SELECT COUNT(*) FROM users WHERE username = ?"; and then separately transmit the value to be used for the parameter symbol. regards, tom lane