pymssql and placeholders

Question

What placeholders can I use with pymssql. I'm getting my values from the html query string so they are all of type string. Is this safe with regard to sql injection?

query = dictify_querystring(Response.QueryString)
employeedata = conn.execute_row("SELECT * FROM employees WHERE company_id=%s and name = %s", (query["id"], query["name"]))

What mechanism is being used in this case to avoid injections?

There isn't much in the way of documentation for pymssql...

Maybe there is a better python module I could use to interface with Sql Server 2005.

Thanks,

Barry

I was missing the parentheses, but I don't need quotes around %s. — Baz, Mar 15 '12 at 18:33
Ah yes, there was no quotes in the linked question either. Should have paid more attention. Sorry for having bothered you needlessly. — Andriy M, Mar 15 '12 at 18:37
But! It might help you in finding out whether your query is safe if you ran SQL Profiler and took a look at the actual query passed to the server. If it looks like `sp_executesql 'your query', '@var definitions', arg values`, then most likely your method is SQL-injection safe. — Andriy M, Mar 15 '12 at 18:42

score 5 · Accepted Answer · answered Mar 15 '12 at 18:37

Regarding SQL injection, and not knowing exactly how that implementation works, I would say that's not safe.

Some simple steps to make it so:

Change that query into a prepared statement (or make sure the implementation internally does so, but doesn't seem like it).
Make sure you use ' around your query arguments.
Validate the expected type of your arguments (if request parameters that should be numeric are indeed numeric, etc).

Mostly... number one is the key. Using prepared statements is the most important and probably easiest line of defense against SQL injection.

Some ORM's take care of some of these issues for you (notice the ample use of the word some), but I would advise making sure you know these problems and how to work around them before using an abstraction like an ORM.

Sooner or later, you need to know what's going on under those wonderful layers of time-saving.

See https://stackoverflow.com/a/3410820/1335793 "Don't be fooled by the %s part: this is NOT string formatting, it's parameter substitution". This kind of parameter substitution is not unique to pymssql, its part of the DB API standard and is _supposed_ to be safe. — Davos, Jan 25 '21 at 10:44

Paulo Scardine · Answer 2 · 2012-03-15T19:08:09.307

Maybe there is a better python module I could use to interface with Sql Server 2005.

Well, my advice is using an ORM like SqlAlchemy to handle this.

>>> from sqlalchemy.ext.sqlsoup import SqlSoup
>>> db = SqlSoup('mssql:///DATABASE?PWD=yourpassword&UID=some_user&dsn=your_dsn')
>>> employeedata = db.employees.filter(db.employees.company_id==query["id"])\
                               .filter(db.employees.name==query["name"]).one()

You can use one() if you want to raise an exception if there is more than one record, .first() if you want just the first record or .all() if you want all records.

As a side benefit, if you later change to other DBMS, the code will remain the same except for the connection URL.

pymssql and placeholders

2 Answers2