SQL injection attack is one of the most popular attacks that are
observed in most of the web applications. This article will be very
useful to the newly joined ELTP's, as they are more prone to write
queries which are vulnerable to SQL Injection attack.In this article, I
will explain what SQL injection is all about and the ways to prevent it.
To begin with, what is SQL Injection (attack)?
As the name suggests, it's an attack which is done with SQL queries. This attack is possible when a web application does not filter the user inputs correctly and trusts whatever the user provides. So in short, SQL forces the application to run queries which were actually not desired i.e a good SQL gone bad :)
Most of the web applications use a form to authenticate users:
When a user clicks the Login button, a request is posted to the action page via HTTP_POST. The action Page has a business logic which authenticates the user by running a query that counts the number of records in the Users table where Username and Password match the values that the user has entered into the form's textbox controls.
Below is the pseudo code. // connection details
Connection.open();
String query = "SELECT Count(*) FROM Users WHERE UserName='" + userName + "' AND Password='" + password + "'"; Where userName and password are the values received from the login POST.
int records = executeQuery(); //execute the above query which will return the result
if (records > 0) {
// success page
}else {
//failure page
}
Connection.close();
In most cases, the form works exactly as intended. A user enters a user name and password that matches a record in the Users table. A dynamically generated SQL query is used to retrieve the number of matching rows. The user is then authenticated and redirected to the requested page. Users who enter an invalid user name or password are not authenticated.
However here, it is also possible for a hacker to enter the following dangerous text into the UserName textbox to gain entry to the system without having to know a valid user name and password ' Or 1=1 --
A hacker can break into the system by injecting malformed SQL into the query. This hack works because the executed query is formed by the concatenation of Strings entered by the user.
If a user enters a valid username and password, let's assume username='durgadas' and password='pass123',then the query becomes SELECT Count(*) FROM Users WHERE UserName='durgadas' AND Password='pass123' - Valid scenario and correct sql.
However when a hacker enters ' Or 1=1 --, The query now becomes,SELECT Count(*) FROM Users WHERE UserName=' ' Or 1=1 - -' AND Password=''. In SQL, a double hyphen (--) indicates beginning of a comment therefore the query actually becomes SELECT Count(*) FROM Users WHERE UserName='' Or 1=1
The expression 1=1 is always true for every row in the table, and a true expression or'd with another expression will always return true. So, assuming there's at least one row in the Users table, this SQL will always return records greater than 0 which will eventually pass the authentication and redirectto the success page as per the login.
Not all SQL injection attacks involve forms authentication. All it takes is an application with some dynamically constructed SQL and un-trusted user input.
Most SQL compliant databases stores metadata in a series of system tables with the names sysobjects, syscolumns, sysindexes, and so on. This means that a hacker could use the system tables to ascertain schema information for a database to assist in further compromise of the database.
For e.g. 'UNION SELECT id, name, '', 0 FROM sysobjects WHERE xtype ='U' -
The UNION statement is very powerful since it allows the hacker to splice the results of one query onto another. In this case, the hacker has spliced the names of the user tables in the database to the original query before the UNION statement. The trick here is to match the number and data types of the columns to the original query.SQL injection attacks can also be used to change data or damage the database i.e. update records, drop table etc.
SQL injection attack is not just limited to specific databases. In fact, all databases such as SQL Server, DB2, Oracle, and Sybase are susceptible to this type of attacks.
Certain ways by which you can prevent SQL Injection attack:
To begin with, what is SQL Injection (attack)?
As the name suggests, it's an attack which is done with SQL queries. This attack is possible when a web application does not filter the user inputs correctly and trusts whatever the user provides. So in short, SQL forces the application to run queries which were actually not desired i.e a good SQL gone bad :)
Most of the web applications use a form to authenticate users:
When a user clicks the Login button, a request is posted to the action page via HTTP_POST. The action Page has a business logic which authenticates the user by running a query that counts the number of records in the Users table where Username and Password match the values that the user has entered into the form's textbox controls.
Below is the pseudo code. // connection details
Connection.open();
String query = "SELECT Count(*) FROM Users WHERE UserName='" + userName + "' AND Password='" + password + "'"; Where userName and password are the values received from the login POST.
int records = executeQuery(); //execute the above query which will return the result
if (records > 0) {
// success page
}else {
//failure page
}
Connection.close();
In most cases, the form works exactly as intended. A user enters a user name and password that matches a record in the Users table. A dynamically generated SQL query is used to retrieve the number of matching rows. The user is then authenticated and redirected to the requested page. Users who enter an invalid user name or password are not authenticated.
However here, it is also possible for a hacker to enter the following dangerous text into the UserName textbox to gain entry to the system without having to know a valid user name and password ' Or 1=1 --
A hacker can break into the system by injecting malformed SQL into the query. This hack works because the executed query is formed by the concatenation of Strings entered by the user.
If a user enters a valid username and password, let's assume username='durgadas' and password='pass123',then the query becomes SELECT Count(*) FROM Users WHERE UserName='durgadas' AND Password='pass123' - Valid scenario and correct sql.
However when a hacker enters ' Or 1=1 --, The query now becomes,SELECT Count(*) FROM Users WHERE UserName=' ' Or 1=1 - -' AND Password=''. In SQL, a double hyphen (--) indicates beginning of a comment therefore the query actually becomes SELECT Count(*) FROM Users WHERE UserName='' Or 1=1
The expression 1=1 is always true for every row in the table, and a true expression or'd with another expression will always return true. So, assuming there's at least one row in the Users table, this SQL will always return records greater than 0 which will eventually pass the authentication and redirectto the success page as per the login.
Not all SQL injection attacks involve forms authentication. All it takes is an application with some dynamically constructed SQL and un-trusted user input.
Most SQL compliant databases stores metadata in a series of system tables with the names sysobjects, syscolumns, sysindexes, and so on. This means that a hacker could use the system tables to ascertain schema information for a database to assist in further compromise of the database.
For e.g. 'UNION SELECT id, name, '', 0 FROM sysobjects WHERE xtype ='U' -
The UNION statement is very powerful since it allows the hacker to splice the results of one query onto another. In this case, the hacker has spliced the names of the user tables in the database to the original query before the UNION statement. The trick here is to match the number and data types of the columns to the original query.SQL injection attacks can also be used to change data or damage the database i.e. update records, drop table etc.
SQL injection attack is not just limited to specific databases. In fact, all databases such as SQL Server, DB2, Oracle, and Sybase are susceptible to this type of attacks.
Certain ways by which you can prevent SQL Injection attack:
- Filter user input - Validate all textbox entries using validation controls, regular expressions, code, and so on.
- Never use dynamic SQL - Use parameterized SQL (Prepared statements), Stored procedures etc.
For e.g.
query = conn.prepareStatement (
"SELECT Count(*) FROM Users WHERE UserName =? AND Password =?);
query.setString (1, "durgadas");
query.setString(2, "pass123");
- Don't store secrets in plain text - Encrypt or hash passwords and other sensitive data. Also encrypt connection strings.
- Minimum Information about the Exception - Don't display the error stack trace to the user. Use custom Errors to show only minimum information.
Comments
Post a Comment