Search code examples
pythonsqlitesql-insertsql-injection

How to make the SQL Injection on INSERT work on SQLite

I don't know how to make this SQL Injection work in SQLite. I'm using a function in Python that connects to a database and inserts a string.

I have "database.db" that has two tables: "feedback" and "users".
The feedback table has 1 column: message.
The users table has 2 columns: username and password.

def send_feedback(feedback):
    conn = sqlite3.connect("database.db")
    curs = conn.cursor()
    curs.execute("INSERT INTO feedback VALUES ('%s')" % (feedback))
    print(curs.fetchall())
    conn.close()

I know that the execute function allows me to make a single query to the database, so I can't use ";" to make multiple queries.

What I have tried, is to make the string look like this:

a') SELECT password FROM users --
feedback = "INSERT INTO feedback VALUES ('a') SELECT password FROM users --')"

But this gives me the following error:

sqlite3.OperationalError: near "SELECT": syntax error

So I've tried to use the UNION command:

a') UNION SELECT password FROM users --  
feedback = "INSERT INTO feedback VALUES ('a') UNION SELECT password FROM users --')"

This one works but the fetchall function returns an empty list.

Solution

  • Most SQL injections result in nothing useful to the perpetrator, just a syntax error.

    For example, pass the string "I'm not satisfied" to this feedback function and the extra ' character would cause the quotes to be imbalanced, and this would result in an error, causing the INSERT to fail.

    sqlite3.OperationalError: near "m": syntax error

    That's technically SQL injection. The content interpolated into the query has affected the syntax of the SQL statement. That's all. It doesn't necessarily result in a successful "Mission: Impossible" kind of infiltration.

    Ethan Hunt perpetrating an SQL injection

    I can't think of a way to exploit the INSERT statement you show to make it do something clever, besides causing an error.

    You can't change an INSERT into a SELECT that produces a result set. Even if you try to inject a semicolon followed by a second SQL query, you just get sqlite3.Warning: You can only execute one statement at a time

    Your first try above resulted in a syntax error because you had both a VALUES clause and a SELECT as a source for the data to insert. You can use either one but not both in SQL syntax. See https://www.sqlite.org/lang_insert.html

    You probably already know how to make the code safe, so unsafe content cannot even cause a syntax error. But I'll include it for other readers:

    curs.execute("INSERT INTO feedback VALUES (?)", (feedback,))