In this article, we are going to learn about SQL Injection. We are going to define SQL Injection, look at its common uses, look at a few examples, and its consequences. This article serves as an introduction to SQL injection.

SQL Injection, also known as SQLi, is one of the most common web hacking techniques that hackers use to input malicious SQL statements that may destroy a database or gain them unqualified access/data to the system.

SQL Injection is a common vector attack that may have unlimited gross effects to any organization which includes breach of authentication, integrity, and confidentiality in business concerns. This may result in loss of customer trust and the effects are not favorable in any business environment.

SQL attacks SQL databases and web applications or web sites, which require user input, are the biggest targets.
The history of SQL Injection dates back to the late 90s, and since then it remains a major security concern, even in huge organizations.

SQL Injection allows attackers to override application security parameters and allow them access to confidential information or in some cases delete or tamper with sensitive data to their advantage. According to this, the attack report of 2012 reveals that an average web application receives four attacks per month and in most cases, financial institutions suffer twice as much as other industries.

SQL Queries
Before we get in-depth with SQL Injection, it is best that we understand what SQL is. SQL is an abbreviation for Structured Query Language and is used to communicate with relational databases. SQL has its set of commands and syntax and this is used to manipulate data from a database. SQL commands are used to retrieve, insert, update, or delete data in a database. A simple SQL command is the 'SELECT' statement, which is used to retrieve data from one or more tables. E.g.

This statement will simply retrieve a list of all customers from a table. Likewise, an SQL query may also be used to update or delete data within a table. E.g.

This statement updates the customer with customer number ‘XY99’ to ‘xxxxx’. And likewise,
Will delete all the data in the customers table.

Using such knowledge of SQL we can now explore the effects and uses of SQL Injection in the real world.
Types of SQL Injection
In-band SQLi (Classic)
Inferential SQLi (Blind)
Out-of-band SQLi

SQL Injection in web pages (examples)
SQL injection may occur when a user is required to input some data using given interface controls such as username or a password and the malicious user knowingly inputs an SQL statement such as ‘or 1=1’ in the password field.

Such a query may return a result set as shown below.
This statement may end up giving the malicious user all the user names and passwords in that particular table hence giving him/her access to the application and a lot of damage may result using one malicious SQL statement.

In some cases, it may be because of incorrectly filtered escape characters that the application may end up running malicious queries that may even DROP/UPDATE / ALTER database contents. Such as shown in the example below:

int user_id=getAuthUserid(); 
String query =” Select * from tbl_users where user_id = '" + user_id + "'; 
” 

The above code intends to get a user’s ID and use it to authenticate the user but if the malicious user then knowingly crafts the user_id variable using any one of the SQL comments(/*,--,{) like as follows to block the rest of the query,
' OR '1'='1'; -- 
' OR '1'='1'; /* 
' OR '1'='1'; { 

Then the query may be executed as:
Select * from tbl_users where user_id='' OR '1'='1'; 

This query will give the malicious user access to all of the table columns and this may result in serious consequences.

Another example includes the use of harmful SQL statement, which drops a table from the database through user input,
It is common practice for many developers to use batch executions and in this case, the attacker may end up deleting all the important data in a given table. In most cases perpetrators of Injection are people with a little bit of expertise in programming and their intentions and knowledge of the application will determine how dangerous they can be once they hack into any system. Apart from Drop/Delete statements, hackers may use select or update SQL statements to obtain or manipulate data in an unfavorable way to cause harm to the application.

The expected result set may be as follows:
As shown in most of the examples above, hackers maybe people with actual intent to cause harm or gain malicious access and they target loose ends such as poor SQL commands on authentication and it is important that prevention measures are taken to avoid SQL Injection.

Results of SQL Injection
Authentication
If the SQL statements used not secure this may lead to hackers getting access to the entire system and damaging the system.

Confidentiality
Since a database always carries sensitive data the advent of a malicious intruder will damage the organization’s reputation.

Authorization
If authorization data is contained within the database they may allow the malicious user to change information and result in the company’s disrepute.

Integrity
Just as it may be possible to read sensitive information, it is also possible to make changes or even delete this information with a SQL Injection attack.

Can SQL Injection be prevented or managed? Yes. We discuss this in my next article SQL Injection Protection Methods and in upcoming articles we also look at an ASP.net example of SQL Injection, Other Injection flaws, Blind, and time-based SQL Injection, and many more.

In this article, we will learn about SQL and SQL Commands. SQL stands for Structured Query Language. SQL is used to create, remove, alter the database and database objects in a database management system and to store, retrieve, update the data in a database. SQL is a standard language for creating, accessing, manipulating database management system. SQL works for all modern relational database management systems, like SQL Server, Oracle, MySQL, etc.

Different types of SQL commands
SQL commands can be categorized into five categories based on their functionality.
 
DDL
DDL stands for data definition language. DDL commands are used for creating and altering the database and database object in the relational database management system, like CREATE DATABASE, CREATE TABLE, ALTER TABLE, etc. The most used DDL commands are CREATE, DROP, ALTER, and TRUNCATE.

    CREATE
    CREATE command is used to create a database and database object like a table, index, view, trigger, stored procedure, etc.
    
    Syntax
    CREATE TABLE Employee (Id INT, Name VARHCAR(50), Address VARCHAR (100));
    
    ALTER
    ALTER command is used to restructure the database object and the settings in the database.
    
    Syntax
    ALTER TABLE Employee ADD Salary INT;
    
    TRUNCATE
    The TRUNCATE command is used to remove all the data from the table. TRUNCATE command empties a table.
    
    Syntax
    TRUNCATE TABLE Employee;
    
    DROP
    DROP command is used to remove the database and database object.
    
    Syntax
    DROP TABLE Employee;

DML
DML stands for data manipulation language. DML commands are used for manipulating data in a relational database management system. DML commands are used for adding, removing, updating data in the database system, like INSERT INTO TableName, DELETE FROM TableName, UPDATE tableName set data, etc. The most used DML commands are INSERT INTO, DELETE FROM, UPDATE.

    INSERT INTO
    INSERT INTO command is used to add data to the database table.
    
    Syntax
    INSERT INTO Employee (Id, Name, Address, Salary) VALUES (1, ‘Arvind Singh’, ‘Pune’, 1000);
    
    UPDATE
    UPDATE command is used to update data in the database table. A condition can be added using the WHERE clause to update a specific row.
    
    Syntax
    UPDATE Employee SET Address = ‘Pune India’, Salary = 100 WHERE Id =1;
    
    DELETE
    DELETE command is used to remove data from the database table. A condition can be added using the WHERE clause to remove a specific row which meets the condition.
    
    Syntax
    DELETE FROM Employee WHERE Id =1;

DQL
DQL stands for the data query language. DQL command is used for fetching the data. DQL command is used for selecting data from the table, view, temp table, table variable, etc. There is only one command under DQL which is the SELECT command.
 
Syntax
SELECT * FROM Employee;
 
DCL
DCL stands for data control language. DCL commands are used for providing and taking back the access rights on the database and database objects. DCL command used for controlling user’s access on the data. Most used DCL commands are GRANT and REVOKE.
 
GRANT
GRANT is used to provide access right to the user.
 
Syntax
GRANT INSERT, DELETE ON Employee TO user;
 
REVOKE
REVOKE command is used to take back access right from the user, it cancels access right of the user from the database object.
 
Syntax
REVOKE ALL ON Employee FROM user;
 
TCL
TCL stands for transaction control language. TCL commands are used for handling transactions in the database. Transactions ensure data integrity in the multi-user environment. TCL commands can rollback and commit data modification in the database. The most used TCL commands are COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION.
 
COMMIT
COMMIT command is used to save or apply the modification in the database.
 
ROLLBACK
ROLLBACK command is used to undo the modification.
 
SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction, the transaction can roll back to this point when it's needed.
 
Syntax
Just write COMMIT or ROLLBACK or SAVEPOINT;