1

I'm trying to execute the following query;

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

This query should add different variables into a new row.

However, when I debug the statement (since it does not work) I get the following;

 ERROR: syntax error at or near "in" LINE 1: INSERT INTO INPUT(target, prot, in, out, source, destination... ^

I find this error vague and I'm not sure where to look. Any ideas?

MichaelP
  • 181
  • 5
  • 22
  • 1
    `in` is keyword. This will help you http://stackoverflow.com/questions/7651417/escaping-keyword-like-column-names-in-postgres – slavoo Jan 14 '14 at 09:04
  • Thanks, I've tried to quote it but nothing really changes for some reason! – MichaelP Jan 14 '14 at 09:06
  • `ERROR: syntax error at or near "'in'" LINE 1: INSERT INTO INPUT(target, prot, 'in', out, source, destinati... ^` – MichaelP Jan 14 '14 at 09:08
  • 1
    You need to put reserved words in **double** quotes, not single quotes. Check the manual for details: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS –  Jan 14 '14 at 09:12
  • @a_horse_with_no_name Thanks for your reply, but if I add double quotes, my whole statements gets a big red background in NetBeans. Unexpected identifier in, cannot run at all. – MichaelP Jan 14 '14 at 09:15
  • 1
    That is a PHP(?) problem then. You need to read the PHP manual on how to escape a double quote in a string literal. It *is* the only valid way to escape keywords (see here: http://sqlfiddle.com/#!15/21385/1). As an alternative you could rename the column (`in` is not such a good name anyway). Maybe `amount_in` and `amount_out` or `date_in` and `date_out` (with those two examples you can already see why the names `in` and `out` aren't that good) –  Jan 14 '14 at 09:18
  • @a_horse_with_no_name Thanks, will look into escaping as that seems a very probable cause. Column was generated by IPtables parameters. – MichaelP Jan 14 '14 at 09:21

2 Answers2

1

Try this:

$result = pg_query($ruledbconnection, "INSERT INTO INPUT(target, prot, in, out, source, destination, id) VALUES ('".$target."', '".$protocol."', '".$in."', '".$out."', '".$source."', '".$destination."', '".$id."')");
Emma Paulowicz
  • 316
  • 4
  • 4
1

in is keyword.

Try this:

$result = pg_query($ruledbconnection, "INSERT INTO INPUT(target, prot, \"in\", out, source, destination, id) VALUES ('$target', '$protocol', '$in', '$out', '$source', '$destination', '$id')");
slavoo
  • 5,798
  • 64
  • 37
  • 39