Python syntax: PostgreSQL Query subprocess query, "Error: only ASCII characters allowed"

Question

I am working with the following code in python, calling a PostgreSQL query using subprocess :

 import subprocess claimer_name = 'a_name' startdate = '2014-04-01' enddate = '2018-04-01' data = subprocess.check_output(['/usr/bin/psql -U user_name "SELECT c.asset_id, c.video_id, c.claim_id, c.claim_date FROM db.claim c JOIN db.claim_history h ON c.claim_id = h.claim_id JOIN db.users_email e ON LOWER(e.email) = LOWER(h.email) JOIN m.auth_user u ON e.user_id = u.id WHERE h.list_order = 1 AND c.claim_origin = 'Descriptive Search' AND c.claim_date >= \"%s\" AND c.claim_date < \"%s\" AND concat(u.first_name, concat(chr(32), u.last_name)) = \"%s\""' % (startdate, enddate, claimer_name)], shell=True) 

How can I avoid single quotes around Descriptive Search? Running this as-is code gives the error Only ASCII characters are allowed in an identifier.

I tried:

• [''Descriptive Search'']
• [\'Descriptive Search\']
• [""Descriptive Search""]
• [concat('Descriptive', concat(chr(32), 'Search'))]

and variable assignment: i = 'Descriptive Search' , and then c.claim_origin = \"%s\" .

However, these attempts give the same ASCII characters error. Using string formatting is great for my other variables ( startdate , enddate , claimer_name ), and I'm at a dead end why it doesn't work for the Descriptive Search string.

Using PostgreSQL 9.3.

Any help or points in the right direction would be wonderful; thanks!

Answer 1

There are so many mistakes.

• You should use psycopg2 instead of trying to disable psql to talk to the database;

• Since you are not using the proper database binding, you cannot use the placement parameters (prepared instructions) correctly, so you need to handle escaping in literals yourself to avoid SQL injection risks and citing errors;

• When invoking commands using subprocess avoid using a shell if at all possible. This is another point of possible failure and completely unnecessary in this case;

• Long strings should usually be the """ given in Python to avoid having to hide the nested s;

• The expression concat(u.first_name, concat(chr(32), u.last_name)) uselessly distorted. Just write u.first_name || ' ' || u.last_name u.first_name || ' ' || u.last_name u.first_name || ' ' || u.last_name or format('%s %s', u.first_name, u.last_name) ;

• You use "double quotes" to quote literals that you replace, which is invalid SQL. They will be treated as identifiers in the documentation. Thus, c.claim_date < \"%s\" will fail, for example no column "2014-04-01" ;

• When quoting 'Descriptive Search' you use real single quotes, not apostrophes. By assumption, you edited the code in a text editor, not in a programmer’s text editor. You want the apostrophes 'Descriptive Search' when quoting literals in SQL.

Since you used single quote characters (U + 2018) instead of apostrophes (U + 0027) to quote the Descriptive Search literal string, PostgreSQL did not recognize it as a literal and tried to parse it as an identifier. However, ' not a legal symbol in an unquoted identifier, so it reported an error that you are showing.

See documentation for identifiers and literals .

Here you should have done:

 import psycopg2 import datetime claimer_name = 'a_name' startdate = datetime.date(2014, 1, 1) enddate = datetime.date(2018, 1, 1) conn = psycopg2.connect("user=user_name") curs = conn.cursor() curs.execute(""" SELECT c.asset_id, c.video_id, c.claim_id, c.claim_date FROM db.claim c JOIN db.claim_history h ON c.claim_id = h.claim_id JOIN db.users_email e ON LOWER(e.email) = LOWER(h.email) JOIN m.auth_user u ON e.user_id = u.id WHERE h.list_order = 1 AND c.claim_origin = 'Descriptive Search' AND c.claim_date >= %s AND c.claim_date < %s AND u.first_name || ' ' || u.last_name = %s """, (startdate, enddate, claimer_name) ) results = curs.fetchall() 

Pay particular attention to the fact that I did not use the Python string formatting operator % above. The %s elements are placement parameters that are correctly replaced by psycopg2; see passing parameters to SQL queries .