CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
Aneokoena Aneokoena
Aneokoena Aneokoena

Reputation: 45

My query doesn't work Postgresql-Python

Im having in my database a table with name sounds.Table sounds got these columns :

Start_Time,End_Time,Salience_Label,Class_Label,Bitrate,Bitdepth,Samplerate

I'm using a combobox which values are my column names.I also have an entry where i write some values.I want to query my database (choose something from the combobox and write something in the entry).I get right values from the combobox and from the entry (I print them). So i connecto to db with command 

conn = psycopg2.connect(conn_string)
cursor = conn.cursor() //create cursor

After this i want to create my query string 

 SQL = "SELECT * FROM sounds WHERE "+str(app.box.get())+" = '"+str(entry_text)+"'"

and then i try to query my db with the command 

cursor.execute(SQL)
rows = cursor.fetchall()
for row in rows:
     print row
cursor.close()
conn.close()

i have choose from combobox the value Bitdepth and i have writtern in entry the value 1536. But from the cmd i got this message

ProgrammingError: column "bitdepth" does not exist LINE 1: SELECT * FROM sounds WHERE Bitdepth = '1536'

I know that this is not the correct way to query my db.Please help,thank you!

Upvotes: 3

Views: 1671

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324265

The immediate problem is that your column name is Bitdepth but when you specify Bitdepth as a SQL identifier it is case folded to lower case. You must double quote it to preserve case, writing "Bitdepth" instead.

However, read on.

You're writing insecure code that's vulnerable to SQL injection. Don't use string concatenation to build SQL.

Fixing this will also fix your error. If the column name was constant you'd just write:

SQL = "SELECT * FROM sounds WHERE columnname = %s"
cursor.execute(SQL, (entry_text,))

... but it looks like you are trying to get a column name dynamically from app.box.get(), so this won't work.

Unfortunately you do have to use string concatenation for that, because psycopg2 doesn't expose an identifier quoting method. It's safe if you're careful to quote the identifier, e.g.

quoted_colname = '"' + str(app.box.get()).replace('"','""') + '"'
SQL = "SELECT * FROM sounds WHERE "+quoted_colname+" = %s"
cursor.execute(SQL, (str(app.box.get()), entry_text))

The quoting done there is simple. Wrap the identifier in double quotes, and double any double quotes within the identifier name, so that " becomes "".

That way when a malicious user uses a JavaScript debugger to modify your web form and add a new option ");DROP TABLE sounds;-- to your combo box, you won't lose your data.

Upvotes: 2

Related Questions