Introduction to SQL

SQL, which stands for Structured Query Language, is widely used in web applications to store and retrieve data from databases. SQL is a subtle and complex topic, so, for now, we’ll cover just enough to understand one of the most common database attacks, the SQL injection.

The examples in this chapter are written to work on MySQL, a widely used open-source database.

How SQL works

The first step in using SQL is to establish a connection to the database that people can connect to directly using a SQL client. Usually, people only use a direct connection to do maintenance work like upgrades and to troubleshoot performance issues or bugs. Most connections, however, are performed by other software—for example, a typical web application with the proper credentials. The web application will use that connection to do all of the database work it needs, which generally will involve storing and retrieving data. Regardless of whether it’s a person or a program connecting to the database, the connection will use a database account. Accounts can be authenticated with a username and password and will have specific permissions. A database might give full permissions to an administrative user, for example, but give only limited permissions to another user.

Example

In the SQL model, data is stored in tables. You can think of a table as a grid of data. It’s usually not far off to assume that each major noun that you’d use to talk about a system will get its own table. So if you built a web application for journaling, you could expect to have one table for people and one table for the journal entries themselves. The journal entry table would have one row for each journal entry. The person table would have one row for each person in the system. Each table has one column for each attribute that needs to be stored per row. So we could visualize our schema like this:

journal_entries

JournalEntryId	PersonId	CreatedTimestamp	Body
1	1	2018-01-01 03:00:00	Everybody shim sham!
2	3	2018-01-07 12:34:56	Time for klava.
3	2	2018-01-08 22:14:28	Make no little plans.
4	1	2018-01-08 22:14:37	Time for lindy hop.

person

PersonId	FirstName	LastName
1	Frankie	Manning
2	Daniel	Burnham
3	Vlad	Taltos

Searching for a specific person’s entries

Now that we have all this wonderful data in tables, what can we do with it? Well, one thing we can do is search it. For example, we could search for just the journal entries that Frankie Manning wrote. To do that, we’d write the following SQL: