Table of Contents
Check out this iconic tale of Bobby Tables from the xkcd webcomic and learn how Robert got the name Little Bobby Tables due to a SQL injection vulnerability.
Who is Bobby Tables?
Bobby Tables is a fictional character from the popular webcomic xkcd: Exploits of a Mom, created by Randall Munroe. He is also known as Little Bobby Tables, and his full name is Robert'); DROP TABLE Students;--. His name is a reference to a common type of cyberattack called SQL injection, which exploits a vulnerability in database applications that do not properly sanitize user inputs.
What is SQL Injection?
SQL injection is a technique that allows an attacker to execute arbitrary SQL commands on a database by inserting malicious input into a query string. For example, suppose a web application asks the user to enter their username and password to log in. The application then constructs a SQL query like this:
SELECT * FROM Users WHERE username = '<user input>' AND password = '<user input>';If the user enters a valid username and password, the query will return the corresponding record from the Users table, and the user will be authenticated. However, if the user enters something like this:
username: admin' --password: anything
The query will become:
SELECT * FROM Users WHERE username = 'admin' -- AND password = 'anything';The -- symbol is a comment marker in SQL, which means that everything after it will be ignored by the database. Therefore, the query will effectively become:
SELECT * FROM Users WHERE username = 'admin';This query will return the record of the admin user, regardless of the password entered, and the attacker will gain access to the system as an admin.
Little Bobby Tables: The Tale
SQL injection can also be used to modify or delete data from the database, or even drop entire tables. This is where Bobby Tables comes in. In xkcd comic, a school calls a mother to inform her that they have lost their student records due to a computer problem.
The mother asks:
“Oh Dear, Did he break something?”
And the school replies:
“Did you really name your son Robert’); DROP TABLE Students;– ?“
The mother confirms that she did, and says that they call him Little Bobby Tables. She also hopes that the school has learned to sanitize their database inputs.
The comic implies that the school had a web form that allowed users to enter their names, and then used those names to construct a SQL query like this:
INSERT INTO Students (name, grade) VALUES ('<user input>', '<user input>');If Bobby Tables entered his full name into the form, the query would become:
INSERT INTO Students (name, grade) VALUES ('Robert'); DROP TABLE Students;--', '<user input>');This query would insert a record for Robert into the Students table, then drop the entire table, and then ignore the rest of the query. This would result in losing all the student records from the database.
The comic is humorous because it shows the absurdity of naming a child after a SQL injection attack, and also because it exposes the incompetence of the school’s programmers who failed to prevent such an attack. The comic also serves as an educational tool for programmers who want to learn how to avoid SQL injection in their code.
Preventing SQLi
The best way to prevent SQL injection is to use parameterized queries or prepared statements, which separate the structure of the query from the user input. Parameterized queries use placeholders for user input, and then bind the actual values to those placeholders before executing the query. For example, using parameterized queries, the login query would look something like this:
SELECT * FROM Users WHERE username = ? AND password = ?;The ? symbols are placeholders for user input, which are replaced by the actual values when the query is executed. This way, the user input is treated as literal values, not as part of the query syntax. Therefore, even if the user enters something like admin' --, it will not affect the structure of the query or execute any additional commands.
Using parameterized queries or prepared statements can protect your database from SQL injection attacks in most cases. However, there are some situations where you may need to dynamically construct SQL queries based on user input, such as when you allow users to sort or filter data by different criteria. In those cases, you should still use parameterized queries for any values that come from user input, but you should also validate and sanitize any inputs that are used as part of the query syntax.
For example, if you allow users to choose a column name for sorting data, you should check that the column name is one of the valid options in your database schema, and not something like name; DROP TABLE Users;--.
Conclusion
SQL injection is one of the most common and dangerous types of cyberattacks that can compromise your database and your system. By following best practices for writing secure code and using parameterized queries or prepared statements, you can prevent SQL injection attacks and protect your data from malicious users.
And remember: don’t name your children after SQL injection attacks! 😉