Query error when trying to SELECT data in PostgreSQL 10

Question

I'm just getting started with PostgreSQL and have a bit of a problem. I am using Psycopg2 and hosting the PostgreSQL database on AWS RDS. Here is the query I am trying to execute:

cursor.execute('SELECT id FROM fqdn AS domain WHERE domain.fqdn IS {0}'.format(fqdn_str))

fqdn_str is a domain name like this:

https://www.example.com

and the fqdn table is this:

CREATE TABLE fqdn (
    id SERIAL PRIMARY KEY,
    fqdn TEXT NOT NULL,
    date_last_crawled TIMESTAMP WITH TIME ZONE NOT NULL,
        UNIQUE (fqdn)
);

The error I am getting is this:

psycopg2.ProgrammingError: syntax error at or near "https"

I have no idea what I am doing wrong so I would appreciate a little bit of help with this. If you need any more information then please let me know. Thank you.

Edit:

If I change the format string to %s as shown in the documentation I get the following error:

TypeError: not all arguments converted during string formatting

The error message hints that string literal quotes must be added... try something like cursor.execute('SELECT id FROM fqdn AS domain WHERE domain.fqdn IS \'{0}\''.format(fqdn_str)) — Akina
– Akina, Commented Jan 11, 2019 at 7:50
I suggest you to read Basic module usage. the command above has SQL injection vulnerability. You should use a syntax like cursor.execute('SELECT id FROM fqdn AS domain WHERE domain.fqdn IS %s', (fqdn_str)) — Sahap Asci
– Sahap Asci, Commented Jan 11, 2019 at 7:59
@SahapAsci Thank you for the reply. Unfortunately, if I try and use %s instead, I get the error message shown in my edited question. — Cromulent
– Cromulent, Commented Jan 11, 2019 at 9:15
@SahapAsci I think there needs to be a comma after fqdn_str in the code you suggested in your comment. I added an answer with my reasoning. — Ralf
– Ralf, Commented Jan 11, 2019 at 10:35

Ralf · Accepted Answer · 2019-01-11 10:30:30Z

As user @SahapAsci suggested in a comment to the question, you should use parameters for your SQL query (read the docs) to avoid SQL injection vulnerabilities.

The docs I linked even have a nice note:

But I suspect the code @SahapAsci suggested in their comment has a small error. The user suggested this code:

cursor.execute(
    'SELECT id FROM fqdn AS domain WHERE domain.fqdn IS %s',
    (fqdn_str))

But I think you need to add a comma to the tuple of parameters, because otherwise it won't be a tuple but just a single string, which will be interpreted as an iterable and only use the first char (and then throw the error you mentioned: "TypeError: not all arguments converted during string formatting"). The docs I linked even have a note to be careful about this specific case.

And you probably should us = instead of IS, but I'm not dead certain on that last part.

So your final code should probably look like this:

cursor.execute(
    'SELECT id FROM fqdn AS domain WHERE domain.fqdn = %s',
    (fqdn_str, ))

"But I think you need to add a comma to the tuple of parameters," => it's indeed required, else it's not a tuple. Or you can use a list instead.

McNets · Accepted Answer · 2019-01-11 10:19:25Z

Change IS by = in your WHERE clause.

CREATE TABLE T(ID text);
INSERT INTO T VALUES ('https://www.example.com');

SELECT * FROM T WHERE ID IS 'https://www.example.com';

ERROR: syntax error at or near "'https://www.example.com'"
LINE 1: SELECT * FROM T WHERE ID IS 'https://www.example.com';

SELECT * FROM T WHERE ID = 'https://www.example.com';

| id                      |
| :---------------------- |
| https://www.example.com |

db<>fiddle here

Collectives™ on Stack Overflow

Query error when trying to SELECT data in PostgreSQL 10

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related