PostgreSQL Schema "www" does not exist?

Question

From the PostgreSQL documentation if you do a INSERT without specifying a schema it should be a public schema.

conn = psycopg2.connect(dbname = 'orion',
                host = 'localhost',
                port = 5432,
                user = 'earthling',
                password = 'mysupersecretpassword') 
sql = conn.cursor()

def INSERT(table, info, text):
    date = datetime.date.today()
    query = "INSERT INTO %s (info, text, date) " \
        "VALUES (%s, %s, %s)" %(table, info, text, date)
    sql.execute(query)

INSERT("main", "www.capecod.edu", "test")

For some reason I'm seeing the following error?

psycopg2.ProgrammingError: schema "www" does not exist

score 3 · Accepted Answer · edited May 23 '17 at 11:44

3

You are using string interpolation to create the query. This is what psycopg2 executes:

INSERT INTO main (info, text, date)
    VALUES (www.capecod.edu, test, 2015-09-12)

If it's not obvious what's wrong here, it's that none of the values are quoted. Here is the properly quoted version:

INSERT INTO main (info, text, date)
    VALUES ('www.capecod.edu', 'test', '2015-09-12')

The error is caused by the unquoted www.capecod.edu. Due to the dots, it's being interpreted as schema.table.column.

The "right" way to do this is with a parameterized query.

query = "INSERT INTO main (info, text, date) VALUES (%s, %s, %s)"
params = (info, text, date)
sql.execute(query, params)

psycopg2 will figure out what should be quoted and how. This is a safer option than simply interpolating the string yourself, which often leaves you open to SQL injection attack.

http://initd.org/psycopg/articles/2012/10/01/prepared-statements-psycopg/

Unfortunately, you can't just toss identifiers such as the table name in as a parameter, because then they are quoted as string values, which is bad SQL syntax. I found an answer (python adds "E" to string) that points to psycopg2.extensions.AsIs as a way to pass identifiers such as table names safely as parameters. I wasn't able to make this work in my testing, though.

If you go the AsIs route, you should be cautious about checking the table names are valid, if they somehow come from user input. Something like

valid_tables = ["main", "foo", "bar", "baz"]

if table not in valid_tables:
    return False

edited May 23 '17 at 11:44

Community

1
1

answered Sep 13 '15 at 03:31

Dan Lowe

51,713
20
123
112

This won't work as written. You must interpolate the string yourself, because psycopg2 doesn't know it's an identifier not a literal. – Craig Ringer Sep 13 '15 at 12:37
@CraigRinger Thanks, I hadn't caught that. You can pass the table name in as a param actually, you just have to be more cautious... I will edit the answer. – Dan Lowe Sep 13 '15 at 15:14
After adding the '' around the name it seems to be working only I didn't see the entry in my table? Is this because I need to use params ALWAYS?? Or maybe the date needs to be of PostgreSQL style not python style? – Asher Sep 15 '15 at 00:45
Using params is optional, but encouraged if the data comes from user input (to avoid SQL injection attacks). I can't say more without seeing what you actually did - where did you put the quotes? What is your new code? Please update the question with the new details... – Dan Lowe Sep 15 '15 at 13:34
Its kinda odd I'm doing a INSERT("main", "www.whoi.edu", "test") print SELECT("main", "www.whoi.edu") and I get NONE. My query = "INSERT INTO %s (url, text, date) VALUES ('%s', '%s', '%s')" %(table, url, text, date) – Asher Sep 16 '15 at 03:26
I added the "" around and there seems to be something else... maybe on the DB side since I don't see it. – Asher Sep 16 '15 at 03:27
Using double-quote for strings in a SQL query won't work, you need single-quotes. To SQL, `"main"` is not the same thing as `'main'`. Also you're still using string interpolation, so without extra validation effort, you are still going to be vulnerable to SQL injection attacks. – Dan Lowe Jan 05 '16 at 16:27

PostgreSQL Schema "www" does not exist?

1 Answers1