Python SQLite3 SQL Injection Vulnerable Code

by

An example SQL injection using your first SQL statement:

cursor.execute("insert into user(username, password) values('{0}', '{1}')".format(username, password))

If username and password are "blah" the resulting SQL statement is:

insert into user(username, password) values('blah', 'blah')

and there is no problem with this particular statement.

However, if a user is able to enter a value for password, perhaps from a HTML form, of:

blah'); drop table user; --

the resulting SQL statement will be:

insert into user(username, password) values('blah', 'blah'); drop table user; --

which is actually 3 statements separated by a semicolon: an insert, a drop table, and then a comment. Some databases, e.g. Postgres will execute all of these statements which results in the user table being dropped. Experimenting with SQLite, however, it seems that SQLite will not allow multiple statements at a time to be executed. Nevertheless there might be other ways to inject SQL. OWASP has a good reference on the topic.

Fixing this is easy, use parameterised queries like this:

cursor.execute("insert into user(username, password) values(?, ?)", (username, password))

Placeholders are added to the query using ? and the db engine will properly escape these values to avoid SQL injections. The resultant query will be:

insert into user(username, password) values('blah', 'blah''); drop table users; --')

where the terminating ' in 'blah\'' has been properly escaped. The value 

blah'); drop table users; --

will be present in the password field for the inserted record.

More Related Contents:

  1. Passing table name as a parameter in psycopg2
  2. How can I get dict from sqlite query?
  3. Using a WHERE ___ IN ___ statement
  4. How to get a single result from a SQL query in python?
  5. Is this Python code vulnerable to SQL injection? (SQLite3)
  6. pysqlite: Placeholder substitution for column or table names?
  7. What is an efficient way of inserting thousands of records into an SQLite table using Django?
  8. Python and SQLite: insert into table
  9. How to check the existence of a row in SQLite with Python?
  10. python list in sql query as parameter [duplicate]
  11. Variable table name in sqlite
  12. Django: Deploying an application on Heroku with sqlite3 as the database
  13. How do you escape strings for SQLite table/column names in Python?
  14. Connecting to Microsoft SQL server using Python
  15. How to load existing db file to memory in Python sqlite3?
  16. Sqlite insert query not working with python?
  17. Inserting a table name into a query gives sqlite3.OperationalError: near “?”: syntax error
  18. sqlalchemy: how to join several tables by one query?
  19. Python insert numpy array into sqlite3 database
  20. PYODBC–Data source name not found and no default driver specified
  21. Python Pandas write to sql with NaN values
  22. How to discover table properties from SQLAlchemy mapped object
  23. Parameter unsupported when inserting int
  24. Executing an SQL query over a pandas dataset
  25. SQLite Performance Benchmark — why is :memory: so slow…only 1.5X as fast as disk?
  26. How to perform OR condition in django queryset?
  27. Passing SQLite variables in Python
  28. Python sqlite3 parameterized drop table
  29. How to delete a record in Django models?
  30. Python sqlite3.OperationalError: no such table:

Leave a Comment