How to refactor my python to use a SQL Prepared statement?

Question

Code to accept input from the user and gives a User for a given username. Now it is accepting the input directly into the SQL query but I would like to use it with prepared Statements, how could I do it?

# SELECT QUERIES
def get_all_results(q):
    cur = mysql.connection.cursor()
    cur.execute(q)
    mysql.connection.commit()
    data = cur.fetchall()
    cur.close()
    return data


# UPDATE and INSERT QUERIES
def commit_results(q):
    cur = mysql.connection.cursor()
    cur.execute(q)
    mysql.connection.commit()
    cur.close()


##### Returns a user for a given username
### in: username
### out: User
def get_user(username):
    q = "SELECT * FROM Users"
    q+= " WHERE username = '%s'" % (username)
    logging.debug("get_user query: %s" % q)
    data = get_all_results(q)

    if len(data) == 1:
        user = User(*(data[0]))
        return user
    else:
        logging.debug("get_user: Something wrong happened with (username):(%s)" % (username))
        return None```

The string modulo, `%`, formatting method is no [longer recommended in Python](https://stackoverflow.com/a/13452357/1422451) and should be replaced with `str.format` (v2.6+) or F-strings (v3.6+). — Parfait, Nov 06 '20 at 18:30
Even using `str.format()` is not the way to do prepared statements. If you're interpolating variables into SQL query string yourself, regardless of which string-formatting style you use, that's not going to help protect against SQL injection. — Bill Karwin, Nov 06 '20 at 18:39

score 2 · Accepted Answer · answered Nov 06 '20 at 18:37

Consider adjusting your query methods to accept a parameter input and run in cursor.execute. This requires you replace string formatting of data value in SQL string

" WHERE username = '%s'" % (username)

with a prepared statement of no data that is later binded with data via parameter(s).

" WHERE username = %s"

Do not confuse unquoted %s with quoted '%s' placeholder (which the latter method as commented above is no longer recommended in Python). Altogether:

# SELECT QUERIES
def get_all_results(q, p):                          # ADD NEW INPUT PARAMETER 
    cur = mysql.connection.cursor()
    cur.execute(q, p)                               # PASS BOTH IN EXECUTE CALL
    mysql.connection.commit()
    data = cur.fetchall()
    cur.close()
    return data

# UPDATE and INSERT QUERIES
def commit_results(q, p):                           # ADD NEW INPUT PARAMETER 
    cur = mysql.connection.cursor()
    cur.execute(q, p)                               # PASS BOTH IN EXECUTE CALL
    mysql.connection.commit()
    cur.close()

def get_user(username):
    q = "SELECT * FROM Users WHERE username = %s"   # PREPARED STATEMENT

    logging.debug("get_user query: {}".format(q))

    data = get_all_results(q, (username,))          # PASS PARAMETER AS TUPLE
    # data = get_all_results(q, [username])         # PASS PARAMETER AS LIST

    if len(data) == 1:
        user = User(*(data[0]))
    else:
        msg = "get_user: Something wrong happened with username:{}".format(username)
        logging.debug(msg)
        user = None

    return user

If no parameters is needed for calls pass None type.

data = get_all_results(q, None)

How to refactor my python to use a SQL Prepared statement?

1 Answers1