Postgres INSERT INTO query bug?

Question

What's wrong with the following Postgres query?

INSERT INTO kayak.airports(name, x, y, city) VALUES( $name, $x, $y, $city) 
WHERE airport_id='$airport_id

EDIT (thanks Donnie for helping me make progress) : I tried:

$query="UPDATE kayak.airports SET name=$name, x = $x, y = $y, city = $city
WHERE airports.airport_id='$airport_id'";

It said "column 'Brisbane' doesn't exist" (Brisbane is the first city to be inserted. ) I took out everything between SET and WHERE except for "x=$x" and those were successfully inserted. Ditto for "y=$y". When only leaving in name=$name it says

"Query failed: ERROR: syntax error at or near "International" LINE 1: UPDATE kayak .airports SET name=Brisbane International WHERE... ^"

Michael Trausch · Accepted Answer · 2010-12-17 07:57:04Z

3

Your query string is not quoted. Do not use PHP variable interpolation for building SQL queries, because this will leave your script or application vulnerable to an SQL injection attack.

Instead, use parameterized queries. Thus, your query above becomes:

$query = 'UPDATE kayak.airports SET name = $1, x = $2, y = $3, city = $4'.
         'WHERE airports.airport_id = $5';

Then, you will use the parameterized query calling function pg_query_paramsto pass the required parameters:

$result = pg_query_params($query, $parameters)

Where $parameters is an array of parameters.

Also note that the $query string is single-quoted, because the $n placeholders are not there for interpolation. This prevents any mistakes (such as typing a real variable name by bumping a letter first) and eliminates any possibility of SQL injection.

edited Dec 17, 2010 at 7:57

answered Dec 17, 2010 at 1:36

Michael Trausch

3,1851 gold badge23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user166390 Over a year ago

You can "delete", "edit", then "undelete" a post. It might affect the answer time or have other side-effect though :-/

Michael Trausch Over a year ago

Oh, thanks! I did not know that. I don't care about the answer time, I just hit TAB + space by accident while typing...

Michael Trausch Over a year ago

@Frank: Thanks for the edit. I didn't catch that, though I found the other one... :)

Donnie · Accepted Answer · 2010-12-16 23:47:10Z

1

You're attempting to insert literal values. A where clause makes no sense.

For insert, you can only use where in an insert ... select to limit what the select is returning.

Perhaps you actually want to update an existing record?

answered Dec 16, 2010 at 23:47

Donnie

47k10 gold badges67 silver badges88 bronze badges

Comments

gorthx · Accepted Answer · 2010-12-17 01:29:42Z

For me, if I get an error that a column doesn't exist, it's usually a tipoff that I've quoted something incorrectly (or not at all).

This is borne out by the error message from your attempt to update only the name field:

ERROR: syntax error at or near "International" LINE 1:

(The carat should point right to the problem area in the query.)

The value you are passing to the name field in your UPDATE statement needs to be quoted, just like the value you're passing to airport_id. (I'm going to take a wild guess that x and y are integers, which wouldn't require quoting, which is why you don't get an error when you try to update just those field.) (I'm going to take another wild guess that the value you pass to city will need to be quoted too, but you will probably figure that out shortly. :) )

The end result expanded UPDATE should look something like this:

UPDATE kayak.airports
SET name='Brisbane International', x = 123, y = 456, city = 'Brisbane'
WHERE ...

Collectives™ on Stack Overflow

Postgres INSERT INTO query bug?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related