Introduction to SQL: What is SQL?

SQL (Structured Query Language) is a language for relational database. It allows:

The creation of databases and tables.
Adding records in the form of rows.
The search of data in the base.
Updating data.
Changes in structure of the table (columns).
Management of rights of database users.

The most popular implementation on the Web is MySQL, a free implementation that is used particularly with PHP, but also SQL is the language of many other database software such as PostgreSQL, Oracle, DB2, Access and SQL Server among others...

The main commands are:

CONNECT to etablish a connection to a database.
CREATE to create a new database or a table.
INSERT to add data.
SELECT to make a search in the content.

It is possible to write SQL procedural programs with iterations and conditions.

You can access a database by sending commands as it is done in PHP, or with a visual software such as phpMyAdmin running on the server or locally through XAMPP, as well as many other such local servers.
In this tutorial, we will use MySQL with PHP but also with the phpMyAdmin interface.

A word about SQL injection

Unfortunately, this feature which is to form sentences to make queries can give access to injection of malicious code if you do not take care, a thing you must know before even writing the first line of code.
What is a SQL injection? This can be explained by an example. The user enters text in a form and you must find this text in the database with the SELECT command, of which we will see the definition further.
Suppose the user enters the word "orange". The request built with the form data will be as this:

SELECT * FROM inventory WHERE fruit = orange

This will search the online inventory of fruits to find oranges. Now suppose the user enters the following:

"orange DROP stock".

Here is the new request:

SELECT * FROM inventory WHERE fruit = orange; DROP inventory

The processor interprets commands on the base of keywords that are found in the query, so the word DROP let to delete the table "inventory". This is SQL injection. To prevent this kind of attack, we will always put data in quotation marks:

SELECT * FROM stock WHERE fruit = '$data'

Quotation marks let data to be interpreted as data and not as commands.

A universal language

SQL commands are close to the natural language, it was the goal of the language the principles of which were posed by Edgar F. Codd and applied by IBM SEQUEL (Structured Query Language English), then renamed SQL. But the first commercial version based on Sequel was made by Relational Software, named Oracle now.

ISO SQL-92 or SQL-2 was developed in 1992 and is commonly used. But new standards have succeeded it, not necessarily implemented on all software.
ISO SQL: 2006 can manage XML files, including importing XML data in a database or export its content in XML.
It is especially these standards that make the difference. Whatever software you use, MySQL (or MariaDB), PostgreSQL or Oracle or even NoSQL database managers as Cassandra, you will find the same syntax, with only minor differences.