CODEWITHSUNDEEP
phpsqlpostgresql
MichaelP
MichaelP

Reputation: 181

Relation *tablename* does not exist

Recently I'm getting an error message that I don't know how to deal with. It's very vague.

The PostgreSQL statement I use is:

    $result = pg_query($ruledbconnection, "INSERT INTO INPUT(num, pkts, bytes ,
    target,prot, opt, \"in\", out, source, destination, id) 
    VALUES('$num','$bytes','$pkts','$target', '$opt', '$protocol', '$in', '$out',
   '$source', '$destination', '$id')");

All seems fine, right? However, when I execute this query with variables:

ERROR: syntax error at or near "'INPUT'" LINE 1: INSERT INTO 'INPUT'(num, pkts, bytes ,
target, prot, opt, "i... ^

I've been stuck on this for a while and it might be due escaping in PHP, or maybe something else?

The table that I want to manipulate is called INPUT in my database..

Upvotes: 0

Views: 1215

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324265

The SQL you showed doesn't match the error. The SQL doesn't have quotes around the table name, the error does.

ERROR: syntax error at or near "'INPUT'" LINE 1: INSERT INTO 'INPUT'(num, pkts, bytes ,

So. Single quotes (apostrophes, ') are for SQL values, not identifiers. Identifiers are quoted with double quotes ("). So you'd write:

INSERT INTO "INPUT" (...) VALUES (...)

Note that quoting the table name will preserve case. So if you double quote it here, you must double quote it everywhere you refer to it from. You will save your sanity if you instead just use lower case:

INSERT INTO input (...) VALUES (...)

and even better, a descriptive table name:

INSERT INTO packets_received (...) VALUES (...)

Your syntax error is the least of your problems, though. Let me introduce you to a classic:

SQL Injection - XKCD

Your query follows the pattern:

pg_query($conn, 'INSERT INTO sometable (col) VALUES ($user_input)')

and thus, is a classic example of an SQL injection vulnerability.

Read:

Upvotes: 2

MichaelP
MichaelP

Reputation: 181

Solved by making sure that I escape the quotes around my table name.

"INSERT INTO INPUT (num, pkts, bytes , target, prot, opt, \"in\", out, source, destination, id)

Should be:

"INSERT INTO \"INPUT\" (num, pkts, bytes , target, prot, opt, \"in\", out, source, destination, id)

Upvotes: 0

Related Questions