SOC - Databases and SQL Injection Attack (Lesson)

Databases and SQL Injection Attack

Introduction

Databases are computer programs designed to store lots of data in such a way that finding and retrieving any one piece of data is more efficient than it would have been if you simply dumped the data in a file with no organization. Many industries use databases so they can easily create reports, invoices, search results, and much more.

Database Example Activity

Spreadsheets vs Databases

You may be wondering, how is a database different from a spreadsheet like Google Sheets or Microsoft Excel? Both use columns and rows to store data into organized tables. However, spreadsheets store information in single tables with limited ability to cross-reference or create reports. In contrast, databases can cross-reference to extract data from multiple tables and rapidly produce many types of reports. For example, Google Forms (with MySQL back-end) or Microsoft Access.

The important point here is that data has tremendous value only if there is an accurate and efficient way to extract meaning from it to answer questions like:

What time do most of my customers shop?
What locations are most people searching to travel to?
Who are the top scoring players in this online game?

Database Structure

For example, let’s consider an excerpt from the Sport Data database below.

Sport Data Database
Date	Team	Event	Location	Opponent	Score
3/14	Saints	Season Game	Big HS	Patriots	25 – 14
5/22	Patriots	Semi-Finals	Max Arena	Cougars	11 – 15
7/2	Tigers	Semi-Finals	Short Hall	Saints	45 – 67
…	…	…	…	…	…

Users can pull information from this database, such as:

What are the dates and teams playing in semi-final games?
List all scores for events where the Cougars played.

There are many more combinations! Can you think of any?

Here is another example… Your school records! Here are a few tables that your name could appear in at your school:

Scheduling, like what classes you take, at what time, and what days, etc.
Grades for each course you take
Attendance for each year at the school
Name, grade level, and ID number
Personal information, such as address, parent names, phone numbers, date of birth, etc.
Health information
Transportation routes, bus number, and your pickup location
Sports team rosters, coaches, game dates, etc.

Here are some reports school employees could create if this information is in a database:

Current math grade for any student wanting to take AP Calculus
Students who take the bus AND are on sports teams that practice after school
List of students who earned Honors and have perfect attendance for this grading period, separated by graduation year

Can you think of another useful report?

SQL Databases and Queries

You are probably thinking, so what? Using data from various databases to create useful reports is a complex process that requires computing resources.

SQL stands for Structured Query Language.

It is a computer programming language created for database tasks such as searching, updating, and extracting large amounts of data. Query is a method to get data from a database. For an SQL database, the query must be structured according to the SQL language syntax.

Here is an example:

SQL Syntax: SELECT <what> FROM <database name> WHERE <condition> ;

The simplest SQL Query is to ask for all data in a table. The asterisk * asks for “everything,” such as SELECT * FROM sportsdata;

Let’s go back to our database excerpt…

Sport Data Database
Date	Team	Event	Location	Opponent	Score
3/14	Saints	Season Game	Big HS	Patriots	25 – 14
5/22	Patriots	Semi-Finals	Max Arena	Cougars	11 – 15
7/2	Tigers	Semi-Finals	Short Hall	Saints	45 – 67
…	…	…	…	…	…

A conditional SQL Query is when the query pulls data ONLY when the condition is met.

SELECT <column1, column2> FROM <tablename> WHERE <condition1> ;

Example: I want dates and scores only for events that included the Tigers.

SELECT Date, Score FROM sportsdata WHERE Team=‘Tigers’;

SQL Special Characters

; - end of query (not always necessary)

' - aka tic -- used to open and close a query section; can also be used to enclose a string of characters

“ or () - used to enclose a string of characters

-- or # - anything after is a comment (not part of query code)

- aka wildcard, used to represent everything

Attacking Databases: SQL Injection Video

Attackers use form input fields to create a query that will change the form function in a way not intended by the programmers. Testing a form for SQL Injection Vulnerability involves adding conditional inputs that form “TRUE” statements.

Examples: ‘ or 1=1, “ or 1=1--, ‘ or ‘a’=‘a

This input will be taken by the program and inserted into the existing SQL query’s condition section.

Normal SQL Query: SELECT * FROM SportsData WHERE Team = ‘Patriots‘ ;

SQL Injection Input of a TRUE Statement Query: SELECT * FROM SportsData WHERE Team = ‘Patriots‘ or ‘1’ = ‘1 ‘ ;

SQL Injection Consequences

SQL Injection exploits can result in:

Bypassing authentication
Unintended information disclosure, aka data breach
Data corruption, such as insertion of new records, changes to records, deleting records, or deleting entire tables

"SELECT*
FROM users
WHERE login=$name (SQL injection image) in $name
AND password - $pwd"

Securing against SQL Injection Attacks

Here is how to protect against SQL Injection attacks:

“Sanitize” user input by using programming functions to strip out dangerous characters from the input before it is included in a query.
DISCARD OR BLOCK the input of any characters such as ‘ “ ; #
Protect knowledge about the structure of the database as attackers need this to create queries.
- How many tables? How many columns or rows? What are the column names?
- This can be discovered through SQL Injection trial and error BUT could also be known by ex-employees or outside consultants.

Reflection and Wrap-up

In this lesson, we have learned about the fundamentals of databases, how SQL (Structured Query Language) is utilized to manage and interact with databases, and the critical aspects of SQL injection attacks, including their characteristics and how to mitigate them. Databases are essential tools in various industries for efficiently organizing, storing, and retrieving large volumes of data. Unlike spreadsheets, databases can handle complex queries across multiple tables, making them indispensable for creating detailed reports and analyses. Through examples such as concert ticketing systems, banking records, and school databases, we explored the practical applications of databases in everyday scenarios.

The lesson also covered the basics of SQL, enabling learners to understand how queries are structured and executed. Finally, we delved into SQL injection attacks, highlighting their potential to compromise database security by manipulating queries through form inputs. We discussed the importance of sanitizing user input and protecting knowledge about the database structure as essential practices to safeguard against these attacks. This comprehensive overview equipped you with the knowledge to identify the characteristics of SQL injection attacks and understand the measures needed to prevent them, ensuring the integrity and security of database systems.

[CC BY-NC-SA 4.0 Links to an external site.] UNLESS OTHERWISE NOTED | IMAGES: LICENSED AND USED ACCORDING TO TERMS OF SUBSCRIPTION - INTENDED ONLY FOR USE WITHIN LESSON.