Prevent SQL Injections
Tags: sql, datasecurity, ruby, activerecord
Published: 2019-02-27 00:13:54

---
title: Prevent SQL Injections
published: true
description: How to protect your data from SQL injections
tags: SQL, datasecurity, ruby, activerecord
---

[Stay hydrated out there](https://amzn.to/3S26cJa)

<h2 style="text-align: left;">Protect Your Data from Malicious User Input</h2>
<img class="alignnone size-full wp-image-91 aligncenter" src="https://crishanks.files.wordpress.com/2019/02/robotsports-1.gif" alt="robotsports.gif" width="500" height="375" />

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.

<strong>One example might look like this:</strong>

Player (<span style="text-decoration: underline;">coach input</span>) do (<span style="text-decoration: underline;">coach input</span>). Player (<span style="text-decoration: underline;">coach input</span>) then do (<span style="text-decoration: underline;">coach input</span>).

For example: Player __<span style="text-decoration: underline;">12__,</span>  __<span style="text-decoration: underline;">pass _</span> to Player __<span style="text-decoration: underline;">6</span>__. Player __<span style="text-decoration: underline;">6</span>__, __<span style="text-decoration: underline;">run </span><span style="text-decoration: underline;">pattern 4 _</span>.

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 <span style="text-decoration: underline;">__8__,</span> __<span style="text-decoration: underline;">turn around and dropkick the ball into the rafters</span>__. Players __<span style="text-decoration: underline;">7, 3, 4, and 12</span>__, __g<span style="text-decoration: underline;">o to the locker room and think about what you've done</span>__.

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.
<h3>What is a SQL Injection?</h3>
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.
<h3>Those Pesky Hackers</h3>
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:

<img class="alignnone size-full wp-image-96" src="https://crishanks.files.wordpress.com/2019/02/screen-shot-2019-02-23-at-12.33.14-pm.png" alt="Screen Shot 2019-02-23 at 12.33.14 PM.png" width="1320" height="518" />

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.

<img class="alignnone size-full wp-image-101" src="https://crishanks.files.wordpress.com/2019/02/rich-2.gif" alt="rich.gif" width="500" height="282" />

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:

<img class="alignnone size-full wp-image-102" src="https://crishanks.files.wordpress.com/2019/02/screen-shot-2019-02-23-at-1.03.08-pm.png" alt="Screen Shot 2019-02-23 at 1.03.08 PM.png" width="1158" height="544" />

Since they weren't protected against his SQL injection, no one gets to be a coach in the NRSL!
<h3>How Bad Could it Be?</h3>
Despite its discovery over 15 years ago, SQL injections are still one of the most prevalent threats to websites and apps. Recently, Motherboard reported <a href="https://motherboard.vice.com/en_us/article/vba5nb/fornite-login-hack-epic-games-website" target="_blank" rel="noopener noreferrer">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.</a> Rogue Media Labs discovered <a href="https://roguemedialabs.com/2018/12/20/atlanta-international-airport-hacked-617-57-kb-of-data-including-700-passports-leaked-online/" target="_blank" rel="noopener noreferrer">hackers gained access to hundreds of customer passports</a>. 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.
<h3><strong>Protect Against SQL Injections</strong></h3>
There are <a href="https://www.w3schools.com/sql/sql_injection.asp">a few ways we can protect ourselves</a> against vengeful coaches and other hackers using SQL injections.
<ul>
	<li>Using SQL Parameters allow us to use a '?' and pass in a parameter instead of using string concatenation.</li>
</ul>
<img class="alignnone size-full wp-image-103" src="https://crishanks.files.wordpress.com/2019/02/screen-shot-2019-02-23-at-1.10.10-pm.png" alt="Screen Shot 2019-02-23 at 1.10.10 PM.png" width="1106" height="108" />
<h6><img class="alignnone size-full wp-image-104" src="https://crishanks.files.wordpress.com/2019/02/screen-shot-2019-02-23-at-1.12.33-pm.png" alt="Screen Shot 2019-02-23 at 1.12.33 PM.png" width="1098" height="96" /><a href="https://www.hacksplaining.com/prevention/sql-injection">Screenshots from hacksplaining.com</a></h6>
<ul>
	<li>Provided ActiveRecord methods are pre-built with protected code. Use methods like 'find_or_create_by' instead of SQL queries like 'where'.</li>
	<li>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, <a href="https://medium.com/factory-mind/regex-tutorial-a-simple-cheatsheet-by-examples-649dc1c3f285">use a regex to match</a> for email inputs. Make sure the alphanumerical field inputs don't contain any symbols. Take out any whitespace. There are even cool <a href="https://apidock.com/rails/ActionView/Helpers/SanitizeHelper/sanitize">gems like sanitize</a> that can help.</li>
</ul>
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](https://amzn.to/48GVlcT). 
Click that if you're interested in a good one. 

Or click this for [another cool option](https://amzn.to/48lQTRe).

Or perhaps you have a good one and you want to [spruce it up a bit](https://amzn.to/3RMztpK) (you know I did).