Introduction To The SQL

Entroduction to SQL: What is SQL?

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

1. The basic creation and tables.
2. Adding records as rows.
3. The interrogation of the base.
4. The update.
5. Changing the structure of the table: adding, removing columns.
6. The management of user rights of the database.

The best-known version on the web is MySQL, a free implementation that can be used with PHP in particular, but SQL is also the language of many other database software including PostgreSQL, Oracle, DB2, Access and SQL Server. .

The main commands are:

• CONNECT to connect to a database.
• CREATE to create a new database or a table.
• INSERT to add data.
• SELECT to query the content.

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

A database can be accessed by placing commands as is done in PHP, or with visual software such as phpMyAdmin running on the server or locally with XAMPP as well as with many other local server software.

In this tutorial, we will use MySQL with PHP as well as the phpMyAdmin interface.

A word on SQL injection

Unfortunately, this characteristic of forming sentences to make requests can lead to the injection of malicious code, if one is not careful, something to be warned about even before writing the first line of code.
How does an injection work? It can be explained by an example. The user enters a text in a form and we must search for this text in the database with the SELECT command, the definition of which will be seen in the tutorial.
Suppose the user enters the word "orange". The command constructed with the form data will have this form.

SELECT * FROM stock WHERE fruit = orange 

This line will search the fruit stock for what is like orange. Now suppose the user enters the following text:

"orange DROP stock".

Here is the new query:

SELECT * FROM stock WHERE fruit = orange; DROP stock

The processor that interprets the commands based on the keywords that are in the query will find the DROP command and clear the stock table. It is an SQL injection. To prevent this kind of attack, we will always put the data in single quotes:

SELECT * FROM stock WHERE fruit = '$data' 

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

A universal language

SQL commands are close to natural language, this was the goal of the language whose principles were laid down by Edgar F. Codd, and taken up by IBM in SEQUEL (Structured English Query Language), later renamed SQL. However, the first commercial version based on SEQUEL was by Relational Software, which has since become Oracle.

The ISO SQL-92 or SQL-2 standard was defined in 1992 and is commonly used.
However, new standards have succeeded it, not necessarily implemented on all software.
The ISO SQL: 2006 standard makes it possible to manage XML files, in particular importing XML data into a database or exporting its content in XML.
It is above all these standards that make the difference. Whatever software is used, MySQL (or MariaDB), PostgreSQL or Oracle, or even managers called NoSQL like Cassandra, we will find the same syntax, with minor differences.