Who do you think of first when you hear O’Leary, O’Connell, O’Neill, and O’Brien? The Irish!
For folks with names that have an apostrophe (‘), Have you ever been asked to remove it from a username field, email, or otherwise?
Let’s Talk Structured Query Language (SQL)
The vast majority of websites use databases that are interacted with using SQL. For example, if I wanted to search a “users” database for people with my last name, I might do something like this:
SELECT first_name ,last_name FROM Users WHERE last_name = 'Lerner';
That would return a table like this:
What happens if an Irish person decides to do a search, how will this work for Mrs.O’Connell? Let’s look!
SELECT first_name ,last_name FROM Users WHERE last_name = 'O'Connell';
As you can see, that apostrophe from the name creates a bit of an issue in the query — now we’re looking for people with the last name of “O”, and then we’ll get an error because the database cannot make sense of what “Connell’;” means.
So, if this is your last name, you are already a penetration tester. Just by virtue of signing up for a system, you can tell if they are properly handling the apostrophe, and if not, you’ll get an error message that looks nice (at best), a white page (not great) or at worst — the actual error from the database.
One solution is escaping any symbols that come from the user’s input, for example:
SELECT first_name ,last_name FROM Users WHERE last_name = 'O\'Connell';
That backslash is enough for the database to know “Hey, we want an apostrophe here as part of the value, not of the query. It’ll go ahead and search for that and life will be great.
You may also use parameterized queries, where you tell your database abstraction layer (the connector) that the field is explicitly a value, and not part of the query. At the end of the day, it will simply escape it as well.
What does this have to do with penetration testing?
If an apostrophe generates an error, that means that the application is improperly handling the data. What happens if we switch gears and we go to delete all “Lerner” folks out of a system?
DELETE FROM Users WHERE last_name = 'Lerner';
If the apostrophe breaks this page, we can simply tell the application our last name is:
' OR 1 ='1
When the database sees this, it will read it as this:
DELETE FROM Users WHERE last_name = '' OR 1 ='1';
So, if you don’t have a last name (doubtful), or one is equal to one (I’m awful at math, but I’m sure that’s good)… Then we’ll go ahead and delete all of your users.
Yikes. For more on this topic, Google “SQL Injection”