时间戳

首页 > 解决方案 > 如何重构我的 python 以使用 SQL Prepared 语句？

python - 如何重构我的 python 以使用 SQL Prepared 语句？

问题描述

接受用户输入并为给定用户名提供用户的代码。现在它直接接受输入到 SQL 查询中，但我想将它与准备好的语句一起使用，我该怎么做？

# 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```

标签： pythonmysqlsqlsql-injection

解决方案

考虑调整您的查询方法以接受参数输入并在cursor.execute. 这需要您替换 SQL 字符串中数据值的字符串格式

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

带有一个没有数据的预准备语句，该语句稍后通过参数与数据绑定。

" WHERE username = %s"

不要将 unquoted%s与带引号的'%s'占位符混淆（在 Python 中不再推荐使用上面评论的后一种方法）。共：

# 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

None如果调用传递类型不需要参数。

data = get_all_results(q, None)

推荐阅读