CODEWITHSUNDEEP
sqlpostgresql
cbrulak
cbrulak

Reputation: 15639

error updating row in postgres table

My table is:

CREATE TABLE A(
    id serial NOT NULL,
    date timestamp without time zone,
    type text,
    sub_type text,
    filename text,
    filepath text,
    filesize integer,
);

I have an update routine:

$Query = "UPDATE A SET type=\"" . $strType . "\" where id=" . intval($ID);

Problem:

When $strType is a string, like "lettuce" I get this error:

ERROR:  column "lettuce" does not exist

When it is an int, no error.

Ideas?

more background:

Upvotes: 1

Views: 322

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 755114

Your coding is open to SQL injection. Use placeholders (probably unquoted question marks) and pass the value separately. That avoids the problems at XKCD.

Bill has the answer to your immediate problem about column names vs strings.

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 563021

In SQL, double-quotes are identifier delimiters. Your UPDATE statement is trying to set the type column to the value of the "lettuce" column, which is an error if there is no column named lettuce.

You want to use single-quotes to delimit a string literal in SQL:

$Query = "UPDATE A SET type='" . $strType . "' where id=" . intval($ID);

See also "Do different databases use different name quote?"

Upvotes: 4

Related Questions