Prevent yourself from SQL Injection Attacks!!
Hi Guys,
"This Thread is for Educational purpose only" (The examples given here are very basic... all websites are well protected from these types of simple SQL injection attacks đ, anyways I have to put this line to warn you anyway:sshhhđ
Database security is a huge issue. Having your database compromised is about the worst thing that can happen to a web developer; especially if it contains sensitive user information (passwords, credit card numbers, e-mail addresses etc.). Even if you store your confidential data securely, you donât want anything to be accessible to anyone but yourself. SQL injection attacks are a common vulnerability that many beginning programmers fall victim to. So CE takes you into the SQL injection attacks and prevention methods, so here we go.....
What is SQL Injection?
SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it. Certain SQL Servers such as Microsoft SQL Server contain Stored and Extended Procedures (database server functions). If an attacker can obtain access to these Procedures it may be possible to compromise the entire machine. Attackers commonly insert single qoutes into a URL's query string, or into a forms input field to test for SQL Injection. If an attacker receives an error message like the one below there is a good chance that the application is vulnerable to SQL Injection.
In PHP/MySQL, an SQL injection attack is performed by submitting a particular string through a form that causes your SQL query to behave differently than expected. For example, say you have a login form that checks a username/password combination with a database using a simple SQL query:
The query then becomes:
This is a very basic version of an SQL injection attack but it gives a good illustration of how it works. Youâd be surprised how often this works due to poor code implementation.
Other variations of strings used for SQL injection is as follows:
Setting Maxlength:
The first method Iâll discuss is ineffective but is often suggested. That is setting a maxlength on an input field to disallow users from entering long complex SQL injection attacks. Setting the maxlength attribute on an HTML input field is merely a small obstacle that can be easily circumvented. In fact, anything that is client side is not a solution but merely an inconvenience for an intruder. Not to mention the example in the last article comprised of a mere 8 characters!
Limit Permissions:
The database user that you use to connect to your database should not be set as the top level administrator. Instead, create a new user that contains only the permissions required by your web site. For example, if the front end of your website only reads data from the database then connect to the database with an account that only has SELECT permissions. This method is indeed useful, but with a proper attack, an intruder can create their own superuser from a simple SELECT statement as well.
Turn on Magic Quotes:
Turn on magic quotes in your PHP server settings (thatâs the magic_quotes_gpc variable). What this does is automatically escape quotes and other special characters with a backslash; that way SQL wonât recognize the quote as part of the query and treat it just like any other character. This is automatically done for any HTTP request data including POST, GET and COOKIE. Because it only filters HTTP request data, magic quotes stops most but not all SQL injection attacks! Data passed into SQL statements from the database or files is not filtered and thus can be manipulated to become an SQL injection attack depending on how your site uses this data.
However, this is probably the best solution for beginners. Itâs âset it and forget itâ since all the work is done for you automatically. Unfortunately, if for some reason magic quotes gets turned off (a possibility with managed hosting/shared hosting), your website is suddenly at risk for an SQL injection attack. This is why you should always do some of your own dirty work đ
Do your own Input Cleaning:
Since you can never be sure that magic quotes will stay on, you should always clean up submitted data on your own. This can be done by checking whether magic quotes is on with the get_magic_quotes_gpc() command. If it returns false, you can escape quotes and special characters manually with the simple addslashes command. Itâll look a little something like this:
More attacks and preventions to come..... so stay connected!!!!đ
-Arvind(slashfear)
"This Thread is for Educational purpose only" (The examples given here are very basic... all websites are well protected from these types of simple SQL injection attacks đ, anyways I have to put this line to warn you anyway:sshhhđ
Database security is a huge issue. Having your database compromised is about the worst thing that can happen to a web developer; especially if it contains sensitive user information (passwords, credit card numbers, e-mail addresses etc.). Even if you store your confidential data securely, you donât want anything to be accessible to anyone but yourself. SQL injection attacks are a common vulnerability that many beginning programmers fall victim to. So CE takes you into the SQL injection attacks and prevention methods, so here we go.....
What is SQL Injection?
SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it. Certain SQL Servers such as Microsoft SQL Server contain Stored and Extended Procedures (database server functions). If an attacker can obtain access to these Procedures it may be possible to compromise the entire machine. Attackers commonly insert single qoutes into a URL's query string, or into a forms input field to test for SQL Injection. If an attacker receives an error message like the one below there is a good chance that the application is vulnerable to SQL Injection.
In PHP/MySQL, an SQL injection attack is performed by submitting a particular string through a form that causes your SQL query to behave differently than expected. For example, say you have a login form that checks a username/password combination with a database using a simple SQL query:
[B]SELECT * FROM users WHERE username = â$input_usernameâ AND password = â$input_passwordâ[/B]where $input_username and $input_password are submitted values taken from the login form. So if there is a person with username âadminâ and password âsecretâ, when you enter âadminâ and âsecretâ into the login page, the query becomes:
[B]SELECT * FROM users WHERE username = âadminâ AND password = âsecretâ[/B]The query will indeed find a row with these values and thus will log the user in. The problem arises when an unauthorized user enters âadminâ as the username and something like this as the password: â or 1=1--
The query then becomes:
[B]âSELECT * FROM users WHERE username =âadminâ AND password = ââ OR 1=1--'[/B]This will find a matching row no matter what the username is. The single quote entered as part of the password is added into the query statement and is treated as a closing single quote as opposed to being part of the password. And since 1=1 is always true, your unauthorized user is now logged in as âadminâ without knowing the password. (-- is single line comment, so will comment the last ')
This is a very basic version of an SQL injection attack but it gives a good illustration of how it works. Youâd be surprised how often this works due to poor code implementation.
Other variations of strings used for SQL injection is as follows:
[B]admin'-- ' or 0=0 -- " or 0=0 -- or 0=0 -- ' or 0=0 # " or 0=0 # or 0=0 # ' or 'x'='x " or "x"="x ') or ('x'='x ' or 1=1-- " or 1=1-- or 1=1-- ' or a=a-- " or "a"="a ') or ('a'='a ") or ("a"="a hi" or "a"="a hi" or 1=1 -- hi' or 1=1 -- hi' or 'a'='a hi') or ('a'='a hi") or ("a"="a [/B]Preventing your website from these type of SQL injection:
Setting Maxlength:
The first method Iâll discuss is ineffective but is often suggested. That is setting a maxlength on an input field to disallow users from entering long complex SQL injection attacks. Setting the maxlength attribute on an HTML input field is merely a small obstacle that can be easily circumvented. In fact, anything that is client side is not a solution but merely an inconvenience for an intruder. Not to mention the example in the last article comprised of a mere 8 characters!
Limit Permissions:
The database user that you use to connect to your database should not be set as the top level administrator. Instead, create a new user that contains only the permissions required by your web site. For example, if the front end of your website only reads data from the database then connect to the database with an account that only has SELECT permissions. This method is indeed useful, but with a proper attack, an intruder can create their own superuser from a simple SELECT statement as well.
Turn on Magic Quotes:
Turn on magic quotes in your PHP server settings (thatâs the magic_quotes_gpc variable). What this does is automatically escape quotes and other special characters with a backslash; that way SQL wonât recognize the quote as part of the query and treat it just like any other character. This is automatically done for any HTTP request data including POST, GET and COOKIE. Because it only filters HTTP request data, magic quotes stops most but not all SQL injection attacks! Data passed into SQL statements from the database or files is not filtered and thus can be manipulated to become an SQL injection attack depending on how your site uses this data.
However, this is probably the best solution for beginners. Itâs âset it and forget itâ since all the work is done for you automatically. Unfortunately, if for some reason magic quotes gets turned off (a possibility with managed hosting/shared hosting), your website is suddenly at risk for an SQL injection attack. This is why you should always do some of your own dirty work đ
Do your own Input Cleaning:
Since you can never be sure that magic quotes will stay on, you should always clean up submitted data on your own. This can be done by checking whether magic quotes is on with the get_magic_quotes_gpc() command. If it returns false, you can escape quotes and special characters manually with the simple addslashes command. Itâll look a little something like this:
[B]$username = $_POST['username']; $password = $_POST['password']; if (!get_magic_quotes_gpc()) { $username = addslashes($username); $password = addslashes($password); }[/B]Another method is to assume magic quotes is always off and do your own cleaning for everything. Harry Fuecks from SitePoint came up with this little piece of code to strip any slashes added by magic quotes if it is on. This way you have a guarantee that all data you work with is untouched by magic quotes.
[B]if (get_magic_quotes_gpc()) { $_REQUEST = array_map(âstripslashesâ, $_REQUEST); $_GET = array_map(âstripslashesâ, $_GET); $_POST = array_map(âstripslashesâ, $_POST); $_COOKIE = array_map(âstripslashesâ, $_COOKIE); }[/B]The best method of all is a combination of all the solutions above. If nothing, make sure you understand how magic quotes work instead of simply taking it for granted because one day it will get turned off and youâll be screwed! Magic quotes is your friend but remember, it doesnât prevent all SQL injection attacks. So to be really secure, it is best to do your own cleaning, assuming you do it properly that is!
More attacks and preventions to come..... so stay connected!!!!đ
-Arvind(slashfear)
Replies
-
Ashraf HZimpressive stuff!
-
shalini_goel14Hey slashfear !
Cool information shared man. Looking for information about much better and new types of attacks also. đ
By the way, I have a very small stupid doubt
See
My query for login is like following ok
SELECT * FROM users WHERE username = ${input_username} AND password = ${input_password}
ok , now as a part of trying to do SQL injection attack. I send
input_username=Admin
input_password = ' ' OR 1=1
ok,
So shouldn't the query be like following after going through SQL egine?
SELECT * FROM users WHERE username = 'Admin' AND password = '' ' OR 1=1'
So wouldn't it give query syntax kind of exception? Please clear my doubt đ
Thanks ! -
silverscorpionwell, you shouldn't use 2 single quotes. That's the point here, I think.
If your input was like this,
input_username=Admin
input_password = ' OR 1=1
then, after going thru the sql engine, it'll be modified to
SELECT * FROM users WHERE username = 'Admin' AND password = ' ' OR 1=1'
Now the attack can happen. Am I clear?? -
Saandeep SreerambatlaVery nice information shared SlashFear.
-
slashfearHi guys,
Thanks .....ASH, Shalini, Scorpion and ES for the support đ
@shalini The explanation is below
In the next few examples you will get clear picture.... ;-)
-Arvind(slashfear) -
slashfearHi Guys,
We have already discussed about what is SQL injection and how the attack is carried out, now lets get a little more deeper into how the attack works with some examples for you guys to get a crystal clear picture ;-)(Please try these examples, so that you can understand better :smileđ of how it works....
As we know, SQL Injection occurs when an attacker is able to insert a series of SQL statements into a 'query' by manipulating data input into an application.
Let us take a typical SQL statement like this:
[B]select id, forename, surname from authors[/B]
This statement will retrieve the 'id', 'forename' and 'surname' columns from the 'authors' table, returning all rows in the table. The 'result set' could be restricted to a specific 'author' like this:
[B] select id, forename, surname from authors where forename = 'john' and surname = 'smith'[/B]
An important point to note here is that the string literals 'john' and 'smith' are delimited with single quotes. Presuming that the 'forename' and 'surname' fields are being gathered from user-supplied input, an attacker might be able to 'inject' some SQL into this query, by inputting values into the application like this:
[B] Forename: jo'hn Surname: smith[/B]
Then 'query string' becomes this:
[B] select id, forename, surname from authors where forename = 'jo'hn' and surname = 'smith'[/B]
When the database attempts to run this query, it is likely to return an error as follows:
[B] Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'hn'.[/B]
The reason for this is that the insertion of the 'single quote' character 'breaks out' of the single-quote delimited data. The database then tried to execute 'hn' and failed.
If the attacker specified input like this:
[B] Forename: jo'; drop table authors-- Surname:[/B]
...the authors table would be deleted, for reasons that we will go into later with an example.
It would seem that some method of either removing single quotes from the input, or 'escaping' them in some way would handle this problem. This is true, but there are several difficulties with this method as a solution. First, not all user-supplied data is in the form of strings. If our user input could select an author by 'id' (presumably a number) for example, our query might look like this:
[B]select id, forename, surname from authors where id=1234[/B]
In this situation an attacker can simply append SQL statements on the end of the numeric input. In other SQL dialects, various delimiters are used; in the Microsoft Jet DBMS engine, for example, dates can be delimited with the '#' character. Second, 'escaping' single quotes is not necessarily the simple cure it might initially seem, for reasons we will go into later.
We Discuss these points in further detail using a sample Active Server Pages (ASP) 'login' page, which accesses a SQL Server database and attempts to authenticate access to some fictional application.
Below is the code for the 'form' page, into which the user types a username and password:
[B]
Below is the code for 'process_login.asp', which handles the actual login:Login Page Login
[/B]
[B] <%@LANGUAGE = JScript %> <% function trace( str ) { if( Request.form("debug") == "true" ) Response.write( str ); } function Login( cn ) { var username; var password; username = Request.form("username"); password = Request.form("password"); var rso = Server.CreateObject("ADODB.Recordset"); var sql = "select * from users where username = '" + username + "' and password = '" + password + "'"; trace( "query: " + sql ); rso.open( sql, cn ); if (rso.EOF) { rso.close(); %>
ACCESS DENIED ACCESS GRANTED
Welcome, <% Response.write(rso("Username")); Response.write( "