CODEWITHSUNDEEP
pythonsql
David Wolever
David Wolever

Reputation: 154494

Sanitizing user-provided SQL with Python?

I'm working on a small app which will help browse the data generated by vim-logging, and I'd like to allow people to run arbitrary SQL queries against the datasets.

How can I do that safely?

For example, I'd like to let someone enter, say, SELECT file_type, count(*) FROM commands GROUP BY file_type, then send the result back to their web browser.

Upvotes: 4

Views: 20914

Answers (4)

YOU
YOU

Reputation: 123831

In MySQL, you can create a limited user (create new user and grant limited access), which can only access certain table.

Upvotes: 4

user1733352
user1733352

Reputation: 101

Do this:

cmd = "update people set name=%s where id=%s"
curs.execute(cmd, (name, id))

Note that the placeholder syntax depends on the database you are using.

Source and more info here:

http://bobby-tables.com/python.html

Upvotes: 6

Travis Bradshaw
Travis Bradshaw

Reputation: 4320

Consider using SQLAlchemy. While SQLAlchemy is arguably the greatest Object Relational Mapper ever, you certainly don't need to use any of the ORM stuff to take advantage of all of the great Python/SQL work that's been done.

As the introductory documentation suggests:

Most importantly, SQLAlchemy is not just an ORM. Its data abstraction layer allows construction and manipulation of SQL expressions in a platform agnostic way, and offers easy to use and superfast result objects, as well as table creation and schema reflection utilities. No object relational mapping whatsoever is involved until you import the orm package. Or use SQLAlchemy to write your own!

Using SQLAlchemy will give you input sanitation "for free" and let you use standard Python logic to analyze statements for safety without having to do any messy text-parsing/pattern-matching.

Upvotes: 1

Ned Batchelder
Ned Batchelder

Reputation: 375574

Allowing expressive power while preventing destruction is a difficult job. If you let them enter "SELECT .." themselves, you need to prevent them from entering "DELETE .." instead. You can require the statement to begin with "SELECT", but then you also have to be sure it doesn't contain "; DELETE" somewhere in the middle.

The safest thing to do might be to connect to the database with read-only user credentials.

Upvotes: 3

Related Questions