DDL
Data Definition Language (DDL) is the subset of the SQL language that allows table structures to be edited.
The most common DDL commands are:
| Command | Description |
|---|---|
CREATE <table> | Creates a new table. |
ALTER <table> | Changes a table structure, say, to add or remove columns, or indexes. |
DROP <table> | Destroys a table. |
Here is each command at work on a users table:
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
DROP TABLE users;TRUNCATE, which empties a table in one go, also counts as DDL in MySQL, Oracle and SQL Server,
even though its effect looks a lot like a DELETE.
DDL vs DML
DDL changes the shape of the data, and DML changes the data itself. If a database table is a spreadsheet, DDL adds, renames or removes the columns (or the whole sheet), and DML edits the rows. Applications run DML all day long, but they rarely need DDL outside of a release.
DDL and SQL Injection
The webcomic xkcd famously imagined a mother who named her son Robert'); DROP TABLE Students;--,
so that the school's database deleted its student records when it tried to save his name. The joke
works because of a real SQL injection technique. The quote and bracket
close the value the school meant to save, the semicolon ends its statement, and the attacker's
DROP TABLE runs as a second statement. This works when the database driver accepts several
statements in one call, and when the application's database account has DDL privileges.
For most applications, table structures are only changed with the release of new versions of the software. Therefore, the principle of least privilege dictates that the application should be run under a database account that does not have DDL privileges. This defense in depth mitigates the damage an attacker can do if they discover a SQL injection vulnerability in your site. DDL privileges should only be granted temporarily as part of a release process. (Without them, little Bobby Tables gets an error message, and the school keeps its records. It should still fix the injection.)