The structured query language, SQL, is the common language for most relational database systems. The name focuses on queries because database operations retrieve data much more often than they modify data. Relational databases contain one or more data tables, with possible relationships established by matching data in the selected fields. To review, let's look at a single database table. Each table contains records. This table lists articles posted to a blog. There is one record for each article. The different parts of the record are called fields. There's one field for each distinct piece of data in the record. All records in a single table contain the same set of fields. Each field in a table is individually named. All field names in a table are unique. Different tables often use the same field name for similar information. In an SQL table, all fields sharing the same field name make up a column. The select statement forms a query. The simplest example draws data from a single table. We will query the Articles table. The select statement naturally starts with the keyword, select, and next comes a list of the fields we want to retrieve. The asterisk means we want all the fields in the selected record. The ''From'' statement indicates the data table containing the data we're going to look at. The ''Where'' statement chooses which records we want to select. Here we ask for records where author equals Bob. The statement retrieves all the fields in the bottom record. Here's a slightly different select statement and the table it visits. It only retrieves the field containing the article numbers. The ''Into'' keyword identifies a table named Temp to collect the article numbers. The ''From'' and ''Where'' keywords are similar as the ones earlier. Other essential SQL statements are, well, there's the CRUD statements for processing records. Remember, CRUD is create, read, update, delete. There's the insert statement, the select statement, the update statement, and the delete statement. Higher level statements are for creating or backing up databases or for creating or deleting individual tables. We can also modify columns and alter table structures in various ways. When a user logs into an SQL database, the database manager usually restricts which databases the user can visit and often restricts access to particular tables. The access restrictions aren't implemented consistently across all vendors. You need to see what, if any, restrictions a particular database manager might provide. For SQL, vendors might or might not implement statement level restrictions. For example, some users might be restricted to retrieving database information but not be allowed to modify tables or fields. Some users might be allowed to modify fields and records but not allowed to make major changes to the data, to the table, or the database structure. Again, these restrictions vary with the database software being used. We introduced injection attacks in the Cloud security basics course. They take advantage of the software relying on textual data to control what happens. In a typical website content management system, you're relying on three text-based software packages; the web server, the content management system, and the database. Let's see how it's all supposed to work. To login to the website, the user types her ID and password into a browser window or in this case, just the ID. A browser embeds the text in an HTML statement and sends it to the web server. The server invokes the login script in the content management system and passes it in a variable. The ID check function constructs an SQL statement to retrieve the user's record, if the ID matches the record. The result goes into a temporary table called Temp 1. Checking the password follows a similar path, except we check the password against the temporary table we just created. When the user types her password, the browser sends it to the server, which hashes it in the password checking function. Systems that use hashed passwords, and all systems should, will probably pass the hash password in an SQL statement. To keep this a simple example, we won't hash the passwords. The login succeeds if the ''Where'' clause returns true. For example, this ''Where'' clause returns true but that's not the clause the management software generates. We don't know the password. If we're attackers, we don't know the password but maybe we can trick the ''Where'' statement to return true. If we can feed the password function some clever text that tests true, we can log in. Here's the text. The dark red text is the injection attack intended to trick SQL. The attack inserts an OR operation into the SQL expression. Computers interpret OR in a particular way, to combine expressions when at least one is true. If you combine two false statements with OR, the result is false. If you combine two true statements, the result is of course, true. If you combine a true and a false statement, result is also always true. The trick text simply includes a guess at the password and an expression that's always true, combined with the OR operation. This yields a ''Where'' statement that's always true even if we don't guess the password correctly. I've posted a more detailed diagram with this video. Here's some ways to prevent this sort of trickery. First, we make passwords less sensitive to this attack by hashing them. Next, we make the server software less sensitive to injection attacks in general. First, by not putting user text directly into an SQL statement or other language statement. The same problem can arise with the language that the content management system's written in, for example, PHP or one of the newer languages. Sometimes this is the easiest way to do things. If you can't avoid it, clean up the text to emit code syntax and other things that could confuse the software. The major web content management system, so develop tools to block injection attacks. Own them and use them.
