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 Wamp Server, 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.