pg_query won't execute

[Dale Schmitz]

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

[Tom Lane]

Post by Dale Schmitz
The statement "SELECT COUNT(*) FROM users WHERE username = 'john' works just
$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