SQL statement clashes with values being inserted into database

Question

Due to the presence of " ' " in the data that is being inserted into the PostgreSQL database, an error occurs. The error is as follows:

psycopg2.ProgrammingError: syntax error at or near "S" LINE 1: ...ice_type) VALUES('7055598', 'CHEE KONG POI', 'HEE'S ENGINEER...

Is there a way around this problem? The current code is as follows:

def store(license_number, individual_name, corporate_name, reg_address, email_address, land_line, hand_phone_line, work_type):
    statement = (
        "INSERT INTO service_reviews_serviceprovider" \
        " (license_number, individual_name, corporate_name, reg_address, email_address, land_line, hand_phone_line, service_type)" \
        " VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', 'electrician');"
    ).format(license_number, individual_name, corporate_name, reg_address, email_address, land_line, hand_phone_line)

    print(statement)
    cur.execute(statement)
    cur.connection.commit()
    return None

http://stackoverflow.com/questions/897020/a-good-way-to-escape-quotes-in-a-database-query-string — ewcz, Oct 03 '15 at 11:34
Thank you for pointing this out. However, it seems that there are no parameterized statements for psycopg2. — Cloud, Oct 04 '15 at 03:40

Daniel Roseman · Answer 1 · 2015-10-03T13:12:46.110

This is one of the reasons why you don't use string substitution when creating an SQL statement. Use parameters instead:

statement = (   
    "INSERT INTO service_reviews_serviceprovider" 
    " (license_number, individual_name, corporate_name, reg_address, email_address, land_line, hand_phone_line, service_type)"
    " VALUES(%s, %s, %s, %s, %s, %s, %s, 'electrician');"
)

cur.execute(
    statement, (license_number, individual_name, corporate_name, reg_address, email_address, land_line, hand_phone_line)
)

Note that this sends the entire set of parameters as a single tuple argument to the db api, which takes care of quoting as necessary.

As well as fixing your problem, this also guards against SQL injection, which is the main reason why you should always do it this way.

@thebjorn: No, it should not. – Matthias Oct 03 '15 at 13:11 — Matthias, Oct 03 '15 at 13:11

Clodoaldo Neto · Accepted Answer · 2015-10-03T18:37:30.603

1

Use multiline strings """. Use cursor.execute to pass parameters to the query. Turn the to be inserted values into a tuple before passing it to cursor.execute to avoid messy string building and hard to read code.

def store (
    license_number, individual_name, corporate_name, reg_address,
    email_address, land_line, hand_phone_line, work_type
):
    t = (
        license_number, individual_name, corporate_name, reg_address,
        email_address, land_line, hand_phone_line, 'electrician'
    )
    statement = """
        insert into service_reviews_serviceprovider (
            license_number, individual_name, corporate_name, reg_address,
            email_address, land_line, hand_phone_line, service_type
        ) values %s
    ;"""

    print cur.mogrify(statement, (t,))
    cur.execute(statement, (t,))
    cur.connection.commit()
    return None

edited Oct 03 '15 at 18:37

answered Oct 03 '15 at 12:41

Clodoaldo Neto

118,695
26
233
260

why would you create a tuple (`t`) and then pass a tuple of a tuple to `cur.execute(..)`? – thebjorn Oct 03 '15 at 13:00
fwiw, I didn't downvote, I just asked because I've never seen it done that way before and wondered what the purpose was -- as opposed to having a number of `(%s, %s, %s..)` and passing the tuple directly in `cur.execute(stmt, (licence_number, individual_name, ...))` – thebjorn Oct 03 '15 at 16:09

SQL statement clashes with values being inserted into database

2 Answers2