Protect Your Data from Malicious User Input
It's the year 3019 and, naturally, all athletes are robots. You've programmed each robo-player to receive instructions from a coach in order to carry out carefully choreographed plays.
One example might look like this:
Player (coach input) do (coach input). Player (coach input) then do (coach input).
For example: Player 12, pass to Player _6. Player 6, __run pattern 4 _.
But bad news. Coach is upset because his players malfunctioned and didn't pour yellow sports drink all over him after last week's big win! Disgruntled, he inputs the following instructions into your program:
Player 8, turn around and dropkick the ball into the rafters. Players 7, 3, 4, and 12, go to the locker room and think about what you've done.
Since the robo-players weren't protected against these inputs, they carried them out! Now you and the entire coaching staff have been fired and no one gets covered with any colored sports drink.
What is a SQL Injection?
SQL (structured query language) is a special programming language used to communicate with a database and tell it what to do. SQL can be used to tell a database to Create, Read, Update and Destroy data (CRUD). When a user enters an SQL command into input fields (such as typical login info like username, password, email, etc.), it will be read by your program and carry out exactly what you've programmed it to do. If left unprotected, your program interacts with that pernicious input and mistakenly allows it to send unintended commands to the database.
Those Pesky Hackers
Let's look at an example. Coach is not pleased about his recent career status, and depression has made it nearly impossible for him to keep it 100 on his Friends fanblog. Three weeks and 15 pounds later, he looks to the National Robo Sports League (NRSL) for revenge.
Coach knows the NRSL website login page has a simple input field for a User Id. The code is set up to take in that string and assign it to a variable, which will then be used to run a simple SQL query. It might look something like this:
The user input string is 105 OR 1=1, which will always be true. Guess what? Coach just got all data in all columns for every user! That could mean passwords, credit card info, and other private information.
Let's take a look at another example.
Until the feds show up, Coach is ballin'. But his lust for revenge is as volatile as a Rachel - Ross romance. He logs back on the the NRSL website and checks out that user input field. This time his input looks something like this:
Since they weren't protected against his SQL injection, no one gets to be a coach in the NRSL!
How Bad Could it Be?
Despite its discovery over 15 years ago, SQL injections are still one of the most prevalent threats to websites and apps. Recently, Motherboard reported cybercriminals were able to use SQL Injections to log in to Fortnite, one of the most popular video games of our time, and take over any player's account. Rogue Media Labs discovered hackers gained access to hundreds of customer passports. These are only a few recent examples. Unprotected, anyone who tries to input data into your program can (intentionally or not) bypass protected fields such as passwords to access private info (credit card numbers, email addresses, etc.), change data as it exists in the database, drop tables or even gain control over your entire system. As we learned in our future sports analogy, SQL injections can ask our programs to carry out embarrassing and harmful commands with serious consequences.
Protect Against SQL Injections
There are a few ways we can protect ourselves against vengeful coaches and other hackers using SQL injections.
- Using SQL Parameters allow us to use a '?' and pass in a parameter instead of using string concatenation.
Screenshots from hacksplaining.com
- Provided ActiveRecord methods are pre-built with protected code. Use methods like 'find_or_create_by' instead of SQL queries like 'where'.
- Sanitize your strings. In other words, inputs like '1=1' for a username seem pretty suspicious... We can run logic that checks and rejects weird inputs. For example, use a regex to match for email inputs. Make sure the alphanumerical field inputs don't contain any symbols. Take out any whitespace. There are even cool gems like sanitize that can help.
SQL injections are not going away any time soon. And neither are disgruntled coaches. Be smart, use protection.
I hope this was helpful
But you know what else is helpful... A shiny new mechanical keyboard.
Click that if you're interested in a good one.
Or click this for another cool option.
Or perhaps you have a good one and you want to spruce it up a bit (you know I did).
Top comments (3)
No! Never sanatize your input! You will forget edge cases. Use prepared statements and nothing else (ORMs like ActiveRecords use prepared statements under the hood)
Thanks for the feedback! Could be a good note to add the pros and cons of sanitized strings.
Good catch - updated