Title: Understand and prevent SQL injection attacks with MariaDB in Python

[SQL injection attacks can corrupt a database]

One of the biggest dangers to poorly implemented database programs is the SQL injection attack. In this attack, a ne'er-do-well enters data into the system that corrupts the database.

In this post, I'll explain how an SQL injection attack works and how you can protect against one in Python. It's really not too hard as long as you understand the danger and know how to deal with it.

SQL Injection

Suppose you have a database program where users enter data. It might use an interface written in tkinter, it might be a web interface, or it might use something else where the users type values and the program takes action.

For example, suppose you have a Delete User screen where the user enters their name to remove their record from the system. You can make similar examples that use other statements like update or create statements, but delete is easy to understand.

The program takes the user's name and composes an SQL DELETE statement like this.

command = f'DELETE FROM People WHERE Name = "{name}"'

If the user enters something normal like James T. Kirk, then the command is DELETE FROM People WHERE Name = "James T. Kirk" and everything works as expected. The program deletes the James T. Kirk record and all's well with the world (or at least the database).

Now suppose the user is a dastardly criminal who enters the bizarre string " OR "" = " for the user name. In that case, the command becomes this:

DELETE FROM People WHERE Name = "" OR "" = ""

In this case, the part of the WHERE clause "" = "" is always true so this command deletes every record in the database!

Command Parameters

You could try to parse the data entered by the user to see if it's safe, but let's face it. Hackers are willing to spend much more time trying to destroy your system than you can afford to spend trying to protect it. They also only have to find a flaw once to cause you misery but you need to be correct under all circumstances, so this approach is somewhat problematic. I'm not saying you can't do it, but there's a much easier way.

First, replace data values in the command with question marks. Then pass the execute method a parameter list holding the values that should be plugged in for the question marks.

Here's an example.

name = '" OR "" = "' command = 'DELETE FROM People WHERE Name = ?' params = [name] print(f'\nCommand: {command}') print(f'Params: {params}') cur.execute(command, params) show_records(cur)

This code pretends the user entered the dangerous value " OR "" = " for the name. The code composes the SQL DELETE command using ? as a placeholder for the name.

Next, the program makes a params list containing the user-entered value. It displays the command and parameter list, and then passes them both to the execute function.

The snippet then displays the database's contents so you can see that the SQL injection attack failed.

Example Code

The following code snippets show how the example demonstrates an SQL injection attack and its prevention. I'll beak the code into sections so it's easy to match the snippets with their outputs.

The following code creates the People table, loads it with some data, and displays the original data.

# Recreate the data. drop_tables(cur) create_tables(cur) insert_records(cur) print('\n*** Initial Data ***') show_records(cur)

Here's the result at this point.

*** Initial Data *** Batman Frodo Baggins James T. Kirk Sherlock Holmes

Next, the program uses the following snippet to show what happens if the user behaves and enters the value James T. Kirk.

# Inline Parameters. print('\n*** Inline Parameters ***') # Delete records where the user enters a safe target name. name = 'James T. Kirk' command = f'DELETE FROM People WHERE Name = "{name}"' print(f'Command: {command}') cur.execute(command) show_records(cur)

It executes the command and displays the following.

*** Inline Parameters *** Command: DELETE FROM People WHERE Name = "James T. Kirk" Batman Frodo Baggins Sherlock Holmes

The James T. Kirk record was deleted and the others remain. So far so boring.

The following snippet shows the SQL injection attack.

# Delete records where the user enters a bad target name. name = '" OR "" = "' command = f'DELETE FROM People WHERE Name = "{name}"' print(f'\nCommand: {command}') cur.execute(command) show_records(cur)

Here's the result.

Command: DELETE FROM People WHERE Name = "" OR "" = "" <no records>

You can see that the People table has been wiped out.

The next snippet shows what happens if you use a parameterized command and the user behaves.

# Parameterized statements. print('\n*** Parameterized statements ***') # Recreate the data. drop_tables(cur) create_tables(cur) insert_records(cur) # Delete records where the user enters a safe target name. name = 'James T. Kirk' command = 'DELETE FROM People WHERE Name = ?' params = [name] print(f'Command: {command}') print(f'Params: {params}') cur.execute(command, params) show_records(cur)

Here's the result.

*** Parameterized statements *** Command: DELETE FROM People WHERE Name = ? Params: ['James T. Kirk'] Batman Frodo Baggins Sherlock Holmes

As in the earlier case when the user behaved, the James T. Kirk record is gone but the others remain.

Finally, here's the code that pits a parameterized command against the SQL injection attack.

# Delete records where the user enters a bad target name. name = '" OR "" = "' command = 'DELETE FROM People WHERE Name = ?' params = [name] print(f'\nCommand: {command}') print(f'Params: {params}') cur.execute(command, params) show_records(cur)

Now the database engine compares in the parameter value to the actual data in the table. In this case, it only affects records where the record's Name value is exactly " OR "" = ", which would be a very strange name indeed.

Here's the output from this test.

Command: DELETE FROM People WHERE Name = ? Params: ['" OR "" = "'] Batman Frodo Baggins Sherlock Holmes

The James T. Kirk record is gone because it was removed by the previous command, but the SQL injection attack has failed to delete the other records.

Conclusion

If a user provides input to the program, you cannot safely plug that input into a database command. You can, however, use a command with parameters to protect against SQL injection attacks.

That doesn't solve all of your problems. Obviously you still need to make the program do what you need it to do. You may also need to worry about DOS (denial of service) attacks, insider threats, physical access, and password spraying, but it does prevent one of the most common remote attacks.

Download the example to experiment with it and to see additional details.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.