In March of 2008, Heartland Payment System was the victim of a data breach which lead to 134 million credit cards being exposed. The attackers used SQL injection to install spyware on Heartland’s data systems. The vulnerability the attackers exploited was nothing new to security experts, yet the company still fell victim to this attack.

Whether you’re storing pictures of cats or credit card information of millions of different users, eventually once your platform gets big enough someone is going to try and break it. The attacker might be a bored twelve year old who downloaded some scripts off of a deep web forum or a team of trained security experts in China attacking your site for political reasons. Whoever it may, and for whatever reason they’re trying to get in, it’s our job as web developers to make our application as resilient as possible to these attacks.

From: http://guides.rubyonrails.org/security.html#injection

So, what is a SQL injection anyways?

A SQL injection is when an end user manipulates database queries being performed on the backend by manipulating the parameters that are sent to the web server. Usually this is done by sending a string via a form that “tricks” the web server into firing SQL statements it did not originally intend to.

A common goal for a malicious end user would be to bypass authorization so they could get access without knowing a correct username or password.

Let’s pretend we’re checking the parameters sent by a web request in order to authenticate a user, which is handled by the create action of our SessionsController.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

class SessionsController < ApplicationController

  def new
  end

  def create
    user = User.find_by("login = '#{params[:name]}' AND password = '#{params[:password]}'")
    if user
      redirect_to :super_secret_documents
    else
      render :login
    end
  end

  def destroy
  end
end

and then let’s pretend we have a simple login form that looks like this:

1
2
3
4
5
6
7

<form action="/create" method="POST">
  First name:<br>
  <input type="text" name="username"><br>
  Last name:<br>
  <input type="text" name="password"><br><br>
  <input type="submit" value="Submit">
</form>

Now, our end user is a big ol' jerk and decides to try and bypass our basic authentication procedure. By entering

‘ OR '1’=‘1

as their username and

‘ OR '2’>‘1

as their password. Now, when these malicious parameters are passed into our User.find_by method in the create action of our SessionsController, the resulting SQL query will be:

1

SELECT * FROM users WHERE login = '' OR '1'='1' AND password = '' OR '2'>'1' LIMIT 1

TODO put in kid giving thumbs up gif

This sql query will ALWAYS return true, as now the query think it needs to check if the login is correct, OR if 1 is equal to one, which will always equate to true. Similarly with the password parameter, the resulting SQL query is now checking whether the correct password was entered OR if 2 is greater than 1, which will also always equate to true.

Bypassing authentication is just one of the many things that can be accomplished with SQL injection. By being creative with what you try and enter into input fields, an end user can do a number of malicious things to your database.

Security Features of Rails

Luckily, Rails has a number of built in security measures to prevent situations like the one we just looked at from occurring. Rails has a built-in filter for special SQL characters, it escapes the ‘, “ , NULL character and line breaks. Whenever you use #Model.find(id) or #Model.find_by_something(something) this security feature is automatically applied. It is important to note though that using #where(”…“), #connection.execute() or Model.find_by_sql() does NOT apply this security measure, as Rails is assuming you are going to sanitize the input yourself. If this security feature was implemented, it would make these methods behave extremely strangely.

One way to easily sanitize this input is to pass an array or hash to the #where method instead of doing string interpolation.

1
2
3
4
5

  Model.where("login = ? AND password = ?", entered_user_name, entered_password).first

  OR

  Model.where(login: entered_user_name, password: entered_password).first

A Historical Example of Another Kind of Injection

“Phreaking is a slang term coined to describe the activity of a culture of people who study, experiment with, or explore telecommunication systems, such as equipment and systems connected to public telephone networks.” - https://en.wikipedia.org/wiki/Phreaking

Telecommunication systems used to use “in-band signaling” in order to send control messages to their systems via a phone line. They set certain frequencies to mean certain things, so if I sent a 2000hz tone it might mean “prepare call for routing” while a 2400hz tone might be interpreted by the system as “disconnect caller”. In band signaling means that these control tones were sent on the same channel that voice data was sent. Just like API’s have endpoints which fire different methods or functions on a webserver in order to generate a result, these control tones are the “endpoints” of the telecommunication system.

Let’s look at a frequency detection script I wrote using Python to visualize how this might work:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

__author__ = 'markdegroat'
import pyaudio
import wave
import numpy as np
import sys
import time
import timeit

chunkToAnalyze = 1024
if len(sys.argv) < 2:
        print("Plays a wave file.\n\nUsage: %s filename.wav" % sys.argv[0])
        sys.exit(-1)
# instantiate PyAudio (1)
p = pyaudio.PyAudio()
wav = wave.open(sys.argv[1], 'rb')
sampleWidth = wav.getsampwidth()
sampleRate = wav.getframerate()
sampleChannels = wav.getnchannels()
#Use a windowing function to eliminate leakage that is not the dom freq
#windowing function is taken from the numpy library
window = np.blackman(chunkToAnalyze)
stream=p.open(format=p.get_format_from_width(wav.getsampwidth()),
        channels=sampleChannels,
         rate=sampleRate,
        output=True)
#begin to analyze chunk
data = wav.readframes(chunkToAnalyze)
#play stream and find the frequency of each chunk

#this step writes data out to the audio stream
# unpack the data and times by the hamming window
indata = np.array(wave.struct.unpack("%dh" % (len(data)/sampleWidth),\
                                     data))*window
# Take the fft and square each value
fftData = abs(np.fft.rfft(indata))**2
# find the maximum, aka find the bin with the most "hits"
which = fftData[1:].argmax() + 1
detectedFrequency = which*sampleRate/chunkToAnalyze


print "The freq is estimated to be about %f Hz."%(detectedFrequency)

if (detectedFrequency > 2550 && detectedFrequency < 2650){
  # END CALL
  #Here we would send a message to the telecommunication system
  #to hang up the line
}
if (detectedFrequency > 4550 && detectedFrequency < 4650){
  # END CALL
  #Here we would send a message to the telecommunication system
  #to prepare the line for a new call
}

https://en.wikipedia.org/wiki/John_Draper#Phreaking

http://mentalfloss.com/article/19484/true-crime-john-draper-original-whistle-blower

sources: http://www.dotnettricks.com/learn/webapi/what-is-web-api-and-why-to-use-it-