Generate SQL statements with python

Question

I need to generate a list of insert statements (for postgresql) from html files, is there a library available for python to help me properly escape and quote the names/values? in PHP i use PDO to do the escaping and quoting, is there any equivalent library for python?

Edit: I need to generate a file with sql statements for execution later

Generating "plain SQL statements" with data in-line is inherently prone to security failures for reasons as abstract as varying interpretations of Unicode characters between the database engine and the library doing the generation. It is NOT something you should try to do. The output, therefore, should be phrased -- and passed to your database API -- as a query string / data list combo. SQLAlchemy will automate this. — Charles Duffy, Oct 14 '09 at 04:24
I understand, but the use case was generating an SQL file to be executed later — Jeffrey04, Mar 04 '14 at 07:45
http://stackoverflow.com/questions/309945/how-to-quote-a-string-value-explicitly-python-db-api-psycopg2 — John La Rooy, Oct 14 '09 at 02:50

Morten Jensen · Answer 1 · 2022-05-23T19:42:52.347

I know this is an old question, but I've often wanted what it seems the OP wants: A VERY simple library for generating basic SQL.

The below functions do just that. You give them a table name and a dictionary containing the data you want to use and they return the SQL query for the operation you need.

The key/value pairs represent field names and values in the database rows.

def read(table, **kwargs):
    """ Generates SQL for a SELECT statement matching the kwargs passed. """
    sql = list()
    sql.append("SELECT * FROM %s " % table)
    if kwargs:
        sql.append("WHERE " + " AND ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)


def upsert(table, **kwargs):
    """ update/insert rows into objects table (update if the row already exists)
        given the key-value pairs in kwargs """
    keys = ["%s" % k for k in kwargs]
    values = ["'%s'" % v for v in kwargs.values()]
    sql = list()
    sql.append("INSERT INTO %s (" % table)
    sql.append(", ".join(keys))
    sql.append(") VALUES (")
    sql.append(", ".join(values))
    sql.append(") ON DUPLICATE KEY UPDATE ")
    sql.append(", ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)


def delete(table, **kwargs):
    """ deletes rows from table where **kwargs match """
    sql = list()
    sql.append("DELETE FROM %s " % table)
    sql.append("WHERE " + " AND ".join("%s = '%s'" % (k, v) for k, v in kwargs.iteritems()))
    sql.append(";")
    return "".join(sql)

You use it like so. Just give it a table name and a dictionary (or use the **kwargs feature of python):

>>> upsert("tbl", LogID=500, LoggedValue=5)
"INSERT INTO tbl (LogID, LoggedValue) VALUES ('500', '5') ON DUPLICATE KEY UPDATE LogID = '500', LoggedValue = '5';"

>>> read("tbl", **{"username": "morten"})
"SELECT * FROM tbl WHERE username = 'morten';"

>>> read("tbl", **{"user_type": 1, "user_group": "admin"})
"SELECT * FROM tbl WHERE user_type = '1' AND user_group = 'admin';"

But BEWARE OF SQL INJECTION ATTACKS

Look what happens when a malicious user of your code does this:

>>> read("tbl", **{"user_group": "admin'; DROP TABLE tbl; --"})
"SELECT * FROM tbl WHERE user_group = 'admin'; DROP TABLE tbl; --';"

It's easy to make your own makeshift ORM but you only get what you see -- you have to escape the input yourself :)

EDIT:

I'm still using my old library. I've updated it a bit lately: https://github.com/kokke/nano-ORM-py

Here for the insert statements it always generates values inside single quotes. Therefore this code has to be modified as below, ``` keys = ["%s" % k for k in kwargs] # values = ["'%s'" % v for v in kwargs.values()] values = [] for value in kwargs.values(): if type(value) is str: values.append("'%s'" % (value)) elif value is None: values.append("%s" % ("''")) else: values.append("%s" % value) ``` — Senal Weerasinghe, Apr 19 '20 at 11:18
in Python 3+, replace kwargs.iteritems() with kwargs.items() — parvaneh shayegh, Jun 14 '21 at 13:09
10 years later ... it would be better to use `%s = ?` instead of `%s = '%s'`. And you can pass a tuple of params to the SQL `execute()` function. The SQL database will automatically sanitize your data (to avoid SQL injection attacks) and it will put quotes around the string as appropriate. — John Henckel, Jun 26 '23 at 14:35
@JohnHenckel I agree with your point and I think this is addressed in the code in the GitHub-repo linked in the bottom of the post, e,g, here https://github.com/kokke/nano-ORM-py/blob/main/nanoorm.py#L28 — Morten Jensen, Jun 26 '23 at 16:00

Charles Duffy · Answer 2 · 2017-06-28T20:09:44.393

14

SQLAlchemy provides a robust expression language for generating SQL from Python.

Like every other well-designed abstraction layer, however, the queries it generates insert data through bind variables rather than through attempting to mix the query language and the data being inserted into a single string. This approach avoids massive security vulnerabilities and is otherwise The Right Thing.

edited Jun 28 '17 at 20:09

answered Oct 14 '09 at 04:21

Charles Duffy

280,126
43
390
441

1

Link is dead. I think this is the relavant document: http://docs.sqlalchemy.org/en/latest/core/expression_api.html – Chris Aug 07 '14 at 22:10
@phasetwenty, thanks, updated (five years later, heh). – Charles Duffy Aug 07 '14 at 22:11
I think it is dead yet again – Umer Hayat Feb 04 '16 at 05:16
Updated to link directly to the root of the SQLAlchemy Core docs. – Charles Duffy Jun 28 '17 at 20:10
1

Link is still working! – Dec 01 '18 at 19:40

score 2 · Answer 3 · answered Oct 14 '09 at 02:35

2

For robustness, I recommend using prepared statements to send user-entered values, no matter what language you use. :-)

answered Oct 14 '09 at 02:35

C. K. Young

219,335
46
382
435

is there already a library to do so? i want plain sql statements as output though :) – Jeffrey04 Oct 14 '09 at 02:52

score 2 · Answer 4 · answered Oct 14 '09 at 02:51

2

The python db api 2.0 has a ".execute" method for connection objects. You can specify parameters (use a comma NOT a % sign to separate params from the query string) with this function.

answered Oct 14 '09 at 02:51

atk

9,244
3
32
32

score 2 · Answer 5 · answered Oct 14 '09 at 04:23

Quoting parameters manually in general is a bad idea. What if there is a mistake in escaping rules? What if escape doesn't match used version of DB? What if you just forget to escape some parameter or erroneously assumed it can't contain data requiring escaping? That all may cause SQL injection vulnerability. Also, DB can have some restrictions on SQL statement length while you need to pass large data chunk for LOB column. That's why Python DB API and most databases (Python DB API module will transparently escape parameters, if database doesn't support this, as early MySQLdb did) allow passing parameters separated from statement:

.execute(operation[,parameters])

I understand, but i just want a list of sql statements (not executing them) — Jeffrey04, Oct 14 '09 at 04:30

Generate SQL statements with python

5 Answers5

Linked