DML
Data Manipulation Language (DML) is the subset of the SQL language that allows querying and updating of table content.
Specifically, it does not allow the editing of table structures; that task is reserved for DDL. The most common DML commands are:
| Command | Description |
|---|---|
SELECT * FROM <table> | Reads data from a table or multiple tables. |
INSERT INTO <table> | Inserts new data into a table. |
UPDATE <table> | Updates data in a table according to supplied criteria. |
DELETE FROM <table> | Deletes 0 or more rows from a table according to supplied criteria. |
Here is each command at work on a users table:
SELECT name, email FROM users WHERE id = 42;
INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
UPDATE users SET email = 'ada@example.org' WHERE id = 42;
DELETE FROM users WHERE id = 42;DML vs DDL
If a database table is a spreadsheet, DML edits the rows, and DDL adds, renames or removes the
columns (or the whole sheet). Some textbooks split the commands further: they put SELECT in a
separate Data Query Language (DQL), and the GRANT and REVOKE commands that manage permissions
in a Data Control Language (DCL). Most developers, and most database documentation, call all four
commands above DML.
DML and SQL Injection
Almost every query an application sends at run time is DML, which makes DML the usual payload of a SQL injection attack. Suppose a login page builds its query by pasting the user's input into a string:
SELECT * FROM users WHERE email = '<email>' AND password = '<password>'An attacker who types ' OR 1=1 -- into the email field turns the query into one that matches every
row in the table, since 1=1 is always true and -- comments out the password check. The
application logs them in as the first user it finds, which is often the administrator. Variations on
the same trick let the attacker read other tables or change the data in them, using whatever
permissions the application's database account has.
DML Permissions
At run time, most applications only need permissions to perform DML operations, meaning application
permissions can be restricted according to the
principle of least privilege. Some classes of applications
can be restricted to read-only DML operations (i.e. SELECT statements), which further
mitigates the damage an attacker can do should they find a vulnerability in the application. In
PostgreSQL, for example:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO web_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;Restricted permissions limit the damage, but they do not stop the injection. To do that, use parameterized statements for every query.