SQL injection in human terms
I recently gave a talk at a conference in Iceland before 150+ middle and top management where as a part of my talk I explained what SQL injection is and how to protect against it. I didn’t think much of it until after the talk when multiple people thanked me for explaining SQL injection in such an understandable way. The compliments I got inspired me to make this quick write up. I hope this will be useful to other people.
Let’s get started
Imagine we have a data-driven application or a website. For our purposes it means that the data which the website (or application) is using is constantly changing. Data is constantly being added, removed or updated. Think Amazon, a phone book or an inventory system. We can use a database to keep track of the information.
A metaphor for a database: I want you to imagine a filing room, a room full of boxes containing folders, which in turn contain papers with information. In one box, you have information on employees (e.g. name, address, email address, phone number and etc.); in another box we have financial information of our company, in a different box we have annual reports, and yet in another box we have some confidential information.
How can applications and webpages communicate with databases? SQL (Structured Query Language) can be used by applications and webpages to communicate with the database. SQL is a query language to communicate with the database. SQL supports commands to add data, remove data, update data and more.
A metaphor for SQL: let’s use English as our query language for the filing room. We have a person working in the filing room called a filing manager. The filing manager understands queries like to Add, Remove and Update information; e.g. if an employee changes his address, we can ask the filing manager to update that particular employee’s record to reflect his new address. Also if an employee leaves the company we can direct the filing manager to remove the information of that employee from our records in the filing room.
Communicating with web pages When we send a query to a webpage, the webpage proxies the query to a database, the database responds to the webpage, which in return forwards the response to us.
In our metaphor I want you to imagine a webpage, which has been constructed for us to look up employee information in our database based on an employee’s name. The web form would contain a field to type an employee name and then a “Search” button next to it.
A metaphor for a webpage communicating with a database: let’s imagine the same filing room as earlier, containing all the same data, including the employee information. Let’s imagine that we create a service window in one of the walls of the filing room. Next we will imagine a software engineer standing in a penguin costume next to the service window. Above the service window, we have written in big clear letters “Get information on employees by submitting an employee name”.
Now let’s assume I walk up to the software engineer in the penguin costume and say to him: Can you give me information on “Svavar Ingi”? He would turn to the filing manager and repeat the query (remember we are using English as our database query language): Can you give me information on “Svavar Ingi”? The filing manager looks through the employee records and finds information the employee and responds to the software engineer (in the penguin costume): “Svavar Ingi Hermannsson, e-mail address: firstname.lastname@example.org, Phone number: XXXX…”, the software engineer turns to me and responds with the same information: “Svavar Ingi Hermannsson, e-mail address: email@example.com, Phone number: XXXX…”.
Now that we’ve covered a metaphor for SQL, let’s get to the SQL injection metaphor.
SQL injection metaphor
If we take a closer look at our previous example, we notice that no safeguards have been put in place. The software engineer has no logic on what he is allowed to repeat to the filing manager, he simply repeats whatever query we give him. The filing manager has also not been given any clear access restrictions.
Assume I would walk up to the software engineer and ask him: ‘Can you give me information on “Svavar Ingi” and can you also give me a copy of last years annual report’? The software engineer (in the penguin costume) would repeat the same request to the filing manager. The filing manager would go through the employee records to find information on Svavar Ingi and then he would also go to the box containing the data on annual reports and give all those information to the software engineer. The software engineer in turn would hand over the information to me.
This is a classic example of an “injection” attack. Assuming the filing manager had enough access and clearance we might also be able to ask him to create a new document, e.g. for a new employee or in any other box for that matter assuming the filing manager had the required access, remove information or update information. Here’s an example for adding information to the employee folder/box: I walk up to the software engineer and give him the following request: ‘Can you give me information on “Svavar Ingi Hermannsson” and can you also create a new employee document for “John Doe”, with address: “Some street, some state, some postal code”, e-mail address: firstname.lastname@example.org, ..’? The software engineer would pass the request along unmodified to the filing manager. The filing manager would create the new employee document and store it in the employee folder/box, then he would look up the information for “Svavar Ingi Hermannsson” and answer the software engineer: “Svavar Ingi Hermannsson, e-mail address: email@example.com, Phone number: XXXX…”, the software engineer would turn to me and give me the same information: “Svavar Ingi Hermannsson, e-mail address: firstname.lastname@example.org, Phone number: XXXX…”.
SQL injection prevention examples
Restrict the filing manager’s access. If you place sensitive material in locked file cabinets and the filing manager doesn’t have the keys he can not retrieve data from them nor add data. You could also give the filing manager strict instructions to never add any new data under any circumstances and only look up data. This is a metaphor for restricting a database users access. Webpage input validation metaphor: We could instruct the software engineer in the penguin costume not to allow any additional queries to be passed to the filing manager. So if we would try to ask the software engineer ‘Can you give me information on “Svavar Ingi” and can you also give me a copy of last years annual report’? The software engineer (in the penguin costume) would respond right away: “No Sir! You can only provide me with a name to look up in our registry. You can not ask me to do additional tasks. Your request is denied.”
As published on Linkedin: https://www.linkedin.com/pulse/sql-injection-human-terms-svavar-ingi-hermannsson