error updating row in postgres table

Question

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:

• the insert code adds filename,filepath, both text types successfully

Answer 1

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?"

Answer 2

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.