What is SQL?
|
|
CREDIT: mitya73 | Shutterstock |
When you visit a website or a blog and log in to view a post or write comments, your login data and comments are handled through a database. SQL, short for Structured Query Language, is a programming language that is designed to store and retrieve that data and other dynamic content within a relational database management structure.
The history of SQL
Much like the origins of the operating system DOS, SQL began back in the days of IBM dominance in the 1970s. While DOS was developed by Microsoft for IBM, IBM was still perfectly capable of creating its own software.
They created a database management system called System R, and to handle the data storage, SQL came into being. At first it was nicknamed SEQUEL (Structured English Query Language), which is still how many pronounce SQL, though later it was simply called SQL.
In 1979, Relational Software, the earliest iteration of the company later to become Oracle, realized the potential of SQL in the commercial software sector and created its own version for distribution called Oracle V2.
Despite being more than 30 years old, SQL is still used in many areas of business. The programming language forms the basis for a variety of well-established database applications on the Internet as well as on individual computers and corporate servers. As database technology progresses, open-source SQL solutions like MySQL, PostgreSQL, SQLite, Firebird and many others allow just about anyone to use the language and create powerful software applications.
The purpose of a database
In the simplest sense, a database is simply a collection of information. In addition to that, a database is designed to handle large quantities of data and store them in an efficient and logical way that ensures information can easily be retrieved. With database software, businesses can keep lists of customers along with any personal information they provide when creating a user account on the company’s website. With such software, forums can identify all of the thread discussions you participated in.
Databases follow a simple table structure with rows and columns, similar to an Excel spreadsheet. The column identifies the data type to be included on subsequent rows. For example, a table including information on customers who shop at an online retail store would have columns for First Name, Last Name, Address, Phone Number and Email Address. Each row would then include each of those details for a customer.
When an SQL query is performed to find all customer emails, SQL locates the table containing customer information, identifies the column for email addresses and retrieves all emails from the rows below that column header.
The SQL Standard
While the language was developed in the 1970s, it continues to go through numerous changes each year. What this means for users is added functionality from support of XML to triggers to new query types. Due to the size of SQL Standard, not all database solutions like MySQL or PostgreSQL implement the entire standard. Even though each of these solutions is based off of the same core programming language, because many offer different standards they often aren’t compatible.
SQL language elements
The programming language, while basic in structure, can still get quite complicated in how each element of the language is used. However, regardless of how complicated the formulas might get for a database, they all ultimately rely on five key commands:
Clauses. These are components of statements and queries that define conditions of how information is to be obtained or presented (e.g., from, where, order by).
Expressions. These create scalar values (Booleans, numbers and characters) or tables with rows and columns for where data is specifically stored.
Predicates. These are used to specify conditions to limit or otherwise direct the effects of a statement or query.
Queries. These will retrieve a specified type of data based on any provided criteria. These are among the most important element of SQL simply because effective database management requires storage and retrieval of data.
Statements. These are the basic framework through which clauses, expressions, and predicates operate. With a statement, a programmer can control connections, sessions, transactions and program flow between the client program and the server where the databases are stored.
SQL queries
As mentioned before, the SQL query is the most common and critical element of any SQL solution. Through the use of a query, a user can search the database for any information needed. The SQL query is quite easily executed through the use of a “SELECT” statement and can be made more specific through the use of clauses. Several examples of clauses include the following:
- FROM. This indicates which table a search must be made in.
- WHERE. This indicates which row in a table the search should take place in. All other rows that do not match the WHERE clause will be excluded from the results.
- ORDER BY. This is the only way to sort results via SQL as the information would otherwise be presented in a random order.
For the vast majority of websites, you don’t need to know SQL in order to use it. Blogging software like WordPress are designed to already handle interactions with databases. Still, a basic understanding for how SQL works can help in better understanding how your website or application is able to store and retrieve data.
SQL injection
Human or automated attackers can send requests that exploit a database's internal codes to alter the query as it's processed. In 2012, SQL injection was the culprit behind a number of notorious security breaches, such as hacker group LulzSec's alleged theft of data from the Sony Pictures server.
Beth Paley, a software training consultant and co-founder of Acrotrex Medical Business Systems in northern New Jersey, offers a solution. Paley advises those who create and maintain database apps to "use whitelisting, not blacklisting," letting only specific data through instead of keeping only specific data out. That way previously unseen SQL injections won't get through. [Related: 10 Computer Threats You Didn't Know About]
