Python and Prepared SQL Statements using Variables

Question

I'm fairly new to Python but have a project I am working on so please excuse any nativity on my part. I am writing some SQL statements in Python 2.7 (Libraries not upgraded to 3 yet) but I am getting stuck on best practice procedure for them. We are using Sybase. Initially I was using

query = "UPDATE DB..TABLE SET version = '{}' WHERE name = '{}'".format(app.version, app.name)
cursor.execute(query)

But realised this after further reading that it is open to injection. So I then looked at doing the following:

query = "UPDATE DB..TABLE SET version = '%s' WHERE name = '%s'" % (app.version, app.name)
cursor.execute(query)

But got me to thinking is this not the same thing?

The parameters are also variables set by argparse, which means I have to use the '' around %s otherwise it throws up the invalid column name error. Which is frustrating for me as I also want to be able to pass NULL (None in Python) by default if any additional flags aren't set in other queries, otherwise it obviously inserts "NULL" as string.

For this particular example the 2 variables are set from a file being read by ConfigParser but I think it's still the same for argparse variables. e.g.

[SECTION]
application=name
version=1.0

I'm not quite sure how to best tackle this issue and yes yes I know "PYTHON 3 IS BETTER UPGRADE TO IT", as I said at the start, the libraries are in the process of being ported.

If you need any additional info then please advise and I will give you the best I can.

UPDATE*** Using the following Param style string I found in some sybase docs it can work but it does not pass None for NULL and throws up errors, starting to think this is a limitation of the sybase module in python.

cursor.execute("SELECT * FROM DB..table where app_name=@app_name", {"@app_name": app_name})
or
params = {"@appname": app.name. "@appver": app.version}
sql = "INSERT INTO DB..table (app_name, app_version) VALUES (@appname, @appversion)
cursor.execute(sql, params)

There is an issue though if you have a global list of params and feed that to a query that if any are None then it gives you a lot of errors again about being None, EVEN if those specific params aren't used in the query. Think I may be stuck doing IF statements for various options here for multiple inserts to bypass this None NULL issue.

Possible duplicate of stackoverflow.com/questions/52481282/… — Ivor Denham-Dyson
– Ivor Denham-Dyson, Commented Jun 21, 2020 at 13:43
I may have figured out a part of it but unsure if this is any safer??? cursor.execute("SELECT * FROM DB..table where app_name=@app_name", {"@app_name": app_name}) — DMurray87
– DMurray87, Commented Jun 21, 2020 at 14:08
Half way there, will require more testing on the NULL insert etc but will mark this as Answered as soon as I have tested. — DMurray87
– DMurray87, Commented Jun 21, 2020 at 14:15
It doesn't like it if you have a value set to None by default but the param is set Exception Sybase.DatabaseError: DatabaseError('Layer: 1, Origin: 1\nct_cmd_drop(): user api layer: external error: This routine can be called only if the command structure is idle.',) in <bound method Cursor.__del__ of Cursor(42361416)> ignored — DMurray87
– DMurray87, Commented Jun 21, 2020 at 14:39
Ok there is a weird issue with Sybase params where they have a length restriction so renaming them to shorter names works on the inserts! But the only issue now is that it doesn't recognise Python's None. It should be auto converting to NULL but it just throws up errors instead — DMurray87
– DMurray87, Commented Jun 22, 2020 at 6:38

John Gordon · Accepted Answer · 2020-06-21 13:31:17Z

1

But got me to thinking is this not the same thing?

Yes, those are effectively the same. They are both wide open to an injection attack.

Instead, do it this way:

query = "UPDATE DB..TABLE SET version = %s WHERE name = %s"
cursor.execute(query, [app.version, app.name])

answered Jun 21, 2020 at 13:31

John Gordon

33.8k9 gold badges48 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DMurray87 Over a year ago

Thanks John, but how would I resolve the issue of having to put single quotes around '%s' to get it to read properly? As well as the NULL issue?

DMurray87 Over a year ago

I also get the following error when trying it your way Incorrect syntax near '%'.

John Gordon Over a year ago

Hmm. It looks like Sybase might use the question mark as the placeholder token. Try replacing %s with ? in the query string.

DMurray87 Over a year ago

Same thing Incorrect syntax near '?' Although in the above examples I gave the %s did work but obviously it is flawed

DMurray87 · Accepted Answer · 2020-06-25 07:50:47Z

1

Ok I have resolved this issue now. I had to update the sybase module in order to get it to work with None > NULL.

As posted in the updated question. the below is how I was running the queries.

cursor.execute("SELECT * FROM DB..table where app_name=@app_name", {"@app_name": app_name})
or
params = {"@appname": app.name. "@appver": app.version}
sql = "INSERT INTO DB..table (app_name, app_version) VALUES (@appname, @appversion)
cursor.execute(sql, params)

answered Jun 25, 2020 at 7:50

DMurray87

211 silver badge5 bronze badges

Collectives™ on Stack Overflow

Python and Prepared SQL Statements using Variables

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related