SQL注入漏洞？

Question

我把这个简单的网站放在一起学习，实现一个小的消毒功能

def sqlescape(txt):
    return (str(txt).replace(";",";").replace("'","'"))


def get_cipher(key):
    cipher = crypt.new(str.encode(salt+key))
    return cipher

def encode_body(body, key):
    if key == '':
        body = str.encode(body)
    else:
        cipher = get_cipher(key)
        body = cipher.encrypt(body)
    return base64.b64encode(body)


def decode_body(body, key):
    body = (base64.b64decode(body)).decode()
    if not key == '':
        cipher = get_cipher(key)
        body = cipher.decrypt(body)
        body = body.decode()
    return body


@app.route('/')
def home():
    return render_template('home.html')


@app.route('/new')
def new_task():
    return render_template('new_task.html')


@app.route('/addrec', methods=['POST', 'GET'])
def addrec():
    if request.method == 'POST':
        con = sql.connect(db)
        msg = ""
        try:
            title = request.form['title']
            body = request.form['body']
            key = request.form['password']
            body = encode_body(body, key)
            cur = con.cursor()
            cur.executescript("INSERT INTO tasks (title,body) VALUES (" +
                              html.unescape(sqlescape(title)) + "," + html.unescape(sqlescape(body.decode())) +
                              ");")
            con.commit()
            msg = "Record successfully added"
        except:
            con.rollback()
            msg = "error in insert operation"

        finally:
            return render_template("result.html", msg=msg)
            con.close()


@app.route('/task')
def my_route():
    taskid = request.args.get('id', default=1)
    con = sql.connect(db)
    con.row_factory = sql.Row
    cur = con.cursor()
    cur.execute("select title,body from tasks where id=?", taskid)
    (title, body) = cur.fetchone()
    return render_template(
        "task.html",
        title=title,
        body=body,  # body.decode(),
        taskid=taskid)


@app.route('/decrypt', methods=['POST', 'GET'])
def decrypt():
    if request.method == 'POST':
        taskid = request.form['id']
        key = request.form['password']
        con = sql.connect(db)
        con.row_factory = sql.Row
        cur = con.cursor()
        cur.execute("select title,body from tasks where id=?", taskid)
        (title, body) = cur.fetchone()
        body = decode_body(body, key)
        return render_template("decrypt.html", title=title, body=body)


@app.route('/list')
def list():
    con = sql.connect(db)
    con.row_factory = sql.Row
    cur = con.cursor()
    cur.execute("select title,id from tasks")
    rows = cur.fetchall()
    return render_template("list.html", rows=rows)


if not os.path.isfile(db):
    with sql.connect(db) as conn:
        conn.execute(
            'CREATE TABLE tasks (id integer primary key, title TEXT, body TEXT)'
        )

`

使用bandit时，它仍然表示仍然可能存在SQL注入的可能性。我正在寻找任何可能的方式，用户可能是使用SQL注入，可能与一些例子，因为我一直给它自己尝试，但没有用，因为我仍然是新手。任何指示都是很好的。

Answer 1

看起来您的代码很容易受到addrec函数中的SQL注入攻击。特别是，下面的代码行将用户输入直接连接到SQL查询字符串中，允许攻击者插入任意SQL命令：

cur.executescript("INSERT INTO tasks (title,body) VALUES (" +
              html.unescape(sqlescape(title)) + "," + html.unescape(sqlescape(body.decode())) +
              ");")

例如，如果攻击者要为title字段提供以下输入：

'; DROP TABLE tasks; --

生成的SQL查询将是：

INSERT INTO tasks (title,body) VALUES (''; DROP TABLE tasks; --','[base64-encoded body value]');

该查询将首先插入标题值为“；DROP；-”的新记录，然后执行DROP语句来删除整个tasks表。

为了防止SQL注入攻击，您应该使用参数化查询，而不是将用户输入连接到查询字符串中。在Python中，您可以使用？SQL查询中的占位符，并将用户输入作为单独的参数传递给execute或executemany方法。例如，addrec函数可以重写如下：

  @app.route('/addrec', methods=['POST', 'GET'])
def addrec():
    if request.method == 'POST':
        con = sqlite3.connect(db)
        msg = ""
        try:
            # Get the user input
            title = request.form['title']
            body = request.form['body']
            key = request.form['password']
            body = encode_body(body, key)

            # Use a parameterized query to insert the user input into the database
            cur = con.cursor()
            cur.execute("INSERT INTO tasks (title,body) VALUES (?, ?)", (title, body.decode()))

            # Commit the changes to the database
            con.commit()
            msg = "Record successfully added"
        except:
            con.rollback()
            msg = "error in insert operation"

        finally:
            return render_template("result.html", msg=msg)
            con.close()