 |
SQL (Structured Query Language) is a language used for querying databases. It is an imperative language composed by instructions which include clauses and expressions.
There are some SQL sub-languages:
- DML (Data Manipulation Language) is a language to insert, delete and modify data;
- DDL (Data Definition Language) is a language to create, delete and modify data structures (database objects);
- DCL (Data Control Language) is a language to control the privileges for data access;
- DTL (Data Transaction Language) is a language to manage transactions (operations which include more instructions none of which can be executed if one of them fails).
Most database managers implement their own dialect of the language with their own proprietary language extensions. Some standards exist, though:
- SQL86 - First ANSI standard;
- SQL89 - Minor update, by ANSI;
- SQL92 (also called SQL2) - Major update, by ISO;
- SQL:1999 (also called SQL3) - Includes some advanced database objects, which contain database's logic.
- SQL:2003 - Adds XML, sequences and other features.
- SQL:2006 - Advanced XML, X-Query, etc.
- SQL:2008 - Formalized some old proprietary SQL extensions.
Examples
Selecting all rows that meet a certain condition.
SELECT <column names> FROM <tablename> WHERE <condition>;
Inserting a new row into a table
INSERT INTO <tablename> (<column names>) VALUES (<values>);
Deleting all rows in a table that meet a certain condition.
DELETE FROM <tablename> WHERE <condition>;
Updating a content of a row meeting a certain condition.
UPDATE <tablename> SET <values> WHERE <condition>
SQL injection
SQL injection is the most basic security issue caused by sanitized input from users. The only way to defend against them is to use parameterized inputs:
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
Parameters ensure that values are treated literally, and will not be used to execute code.
The exact method used to parameterize SQL is based on the programming language, but is easily learned from the documentation.