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:
[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 HZ
    Ashraf HZ
    impressive stuff!
  • shalini_goel14
    shalini_goel14
    Hey 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 !
  • silverscorpion
    silverscorpion
    well, 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 Sreerambatla
    Saandeep Sreerambatla
    Very nice information shared SlashFear.
  • slashfear
    slashfear
    Hi 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)
  • slashfear
    slashfear
    Hi 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]
    
    
    Login Page
    
    
    
    

    Login

    Username:
    Password:
    [/B]
    Below is the code for 'process_login.asp', which handles the actual login:
    [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

    <% Response.end return; } else { Session("username") = "" + rso("username"); %>

    ACCESS GRANTED

    Welcome, <% Response.write(rso("Username")); Response.write( "" ); Response.end } } function Main() { //Set up connection var username var cn = Server.createobject( "ADODB.Connection" ); cn.connectiontimeout = 20; cn.open( "localserver", "sa", "password" ); username = new String( Request.form("username") ); if( username.length > 0) { Login( cn ); } cn.close(); } Main(); %>[/B]

    The critical point to note here is the part of 'process_login.asp' which creates the 'query string' :
            
    [B]var sql = "select * from users where username = '" + username + "'and password = '" + password + "'";
    [/B]
    If the user specifies the following (Remember this code!!! 😒):
    [B]Username: '; drop table users--
    Password:[/B]
    
    ..the 'users' table will be deleted, denying access to the application for all users. The '--' character sequence is the 'single line comment' sequence in Transact-SQL, and the ';' character denotes the end of one query and the beginning of another. The '--' at the end of the username field is required in order for this particular query to terminate without error.


    The attacker could log on as any user, given that they know the users name, using the following input:
    [B]Username: admin'--[/B]
    
    The attacker could log in as the first user in the 'users' table, with the following input:
    [B]Username: ' or 1=1--[/B]
    
    ...and, strangely, the attacker can log in as an entirely fictional user with the following input:
    [B]Username: ' union select 1, 'fictional_user', 'some_password', 1--[/B]
    
    The reason this works is that the application believes that the 'constant' row that the attacker specified was part of the recordset retrieved from the database.


    Next we will Explore How attackers Obtaining Information Using Error Messages generated by SQL server......😉


    -Arvind(slashfear)
  • shalini_goel14
    shalini_goel14
    Hey Scorpion and slashfear, you could n't understood my question, I said

    if password = ' or 1=1

    and my query is

    select * from users where username='Admin' and password = ' ' or 1=1

    In above query , as per sql engine there should be a closing brace for password by default right? (which I have removed in above query. Then where does that brace go and why?

    According to me following query should n't execute 😕

    SELECT * FROM users WHERE username = 'Admin' AND password = ' ' OR 1=1'
    Please clear .
    PS: One request, can you slow down your pace of giving so much information. I am not able to catch you.
  • slashfear
    slashfear
    Hi Shalini,

    PS: First of all I am really sorry for giving a wrong explanation extremely sorry everyone!!!

    Yes by default there will be a closing brace for password, So for SQL injection to happen we have to do the following change ( I made it by myself) . Let me explain how it works,

    username=Admin
    password = ' OR 1=1--

    The Query becomes like this right,
    
    SELECT * FROM users WHERE username =’admin‘ AND password = ‘[COLOR=Red]‘ OR 1=1--[COLOR=black]'[/COLOR][/COLOR]
    
    The single quote entered as part of the password is added into the query statement and is treated as a closing single quote ' ' (that is red quote indicate the closing quote) and the Double -- is used for single line comment (so all the character after -- will be ignored by the SQL engine) so the closing quote in the program which comes by default will be commented(Smart Right!!!!) and hence ultimately we will have only this query :
    SELECT * FROM users WHERE username =’admin‘ AND password = ‘[COLOR=Red][COLOR=Black]‘ OR 1=1[/COLOR]--'
    [/COLOR]

    will be executed (the red indicates the commented statements)


    P.S: Thank you so much for asking this question shalini , and I am sorry again!! all other attacks are checked and working fine, if you find anything wrong please let me know buddy.


    Guess your question is answered now.......;-)

    And yes I will pull the breaks....... to slow down;-)

    -Arvind(slashfear)
  • shalini_goel14
    shalini_goel14
    Ah, Good efforts put by you slashfear for clearing my doubt but still you didn't get my problem, you are explaining that same thing again and agian which I know. I am not able to understand why that same single quote is working as closing tag for one (password=' ' right ?) and starting tag for other ( ' or 1=1 '). Anyways Leave it, I cannot explain my doubt more than this and I think you will also get tired of explaining to me. 😐
  • Anil Jain
    Anil Jain
    Let me try explan this in another words (Actually I am putting my problem as well).

    I want to split a string on the basis of double quotes (").
    Delimeter as "

    Now in VB syntex would be
    B = "Crazy"BOY"CEAN"

    A = Split (B, """, -1, 1)

    Now this code will give syntex error. My expectation is to give delimeter as ". However VB will understand this as close of quotes and will give syntex error as there will be three double quotes instance.

    Question is what should be the right way to do this. Please clear the doubts. And am sorry if its a bit off the track problem, however I guess Shalini's doubt is on the same lines.

    -CB
  • yamrajbhalla
    yamrajbhalla
    i had a vedio on sq injection
    to try it
    please chk this out #-Link-Snipped-#
  • slashfear
    slashfear
    Hi Shalini,

    Your question is answered , I modified the previous post


    PS: I am sorry the fault is mine 😔

    And Thanks for finding the mistake!!!😲



    -Arvind(slashfear)
  • shalini_goel14
    shalini_goel14
    @CB may be you can use some appropriate escape characters for avoiding syntax error.

    @slashfear Thanks now I am cleared. 😀
  • ms_cs
    ms_cs
    Superb slashfear,
    I have tried it using java,and I have one doubt,
    .....
    .....
    String u2=" ' "; //read the string at runtime
    ResultSet rs=stmt.executeQuery("select * from dbase where u_name='great' and pword='"+u2+"'''");
    .....
    
    when I run this with the input u2='
    I get following error,
    java.sql.SQLException: The string constant beginning with "'''''" does not have an ending string delimiter.
    How to fix this problem?
  • shalini_goel14
    shalini_goel14
    .....
    .....
    String u2=" ' "; //read the string at runtime
    ResultSet rs=stmt.executeQuery("select * from dbase where u_name='great' and pword='"+u2+"'''");
    .....
    
    Man, what you are trying to do exactly, can you Please explain?

    After substituting u2 in sql statement , your query would look something like below to sql engine
    select * from dbase where u_name='great' and pword='''''
    
    Right? I couldn't get your idea of putting so many useless strings. How can it help you out in sql injection attack? It's quite obvious you will get that exception.
  • Saandeep Sreerambatla
    Saandeep Sreerambatla
    crazyboy
    Let me try explan this in another words (Actually I am putting my problem as well).

    I want to split a string on the basis of double quotes (").
    Delimeter as "

    Now in VB syntex would be
    B = "Crazy"BOY"CEAN"

    A = Split (B, """, -1, 1)

    Now this code will give syntex error. My expectation is to give delimeter as ". However VB will understand this as close of quotes and will give syntex error as there will be three double quotes instance.

    Question is what should be the right way to do this. Please clear the doubts. And am sorry if its a bit off the track problem, however I guess Shalini's doubt is on the same lines.

    -CB

    @CB .When you give the input as specified then it will not accept as the input should be changed as
    a = "Crazy""BOY""CEAN"
    b= Split (a , """" , -1,1)

    it gives output as Crazy BOY CEAN

    this is the only way i found , if i dont answer your question please ignore this.
    But in your question the input itself gives a syntax error am i rite??
  • Anil Jain
    Anil Jain
    English-Scared
    @CB .When you give the input as specified then it will not accept as the input should be changed as
    a = "Crazy""BOY""CEAN"
    b= Split (a , """" , -1,1)

    it gives output as Crazy BOY CEAN

    this is the only way i found , if i dont answer your question please ignore this.
    But in your question the input itself gives a syntax error am i rite??
    @ES input looks correct to me both the opening quotes are closed.

    a = "Crazy""BOY""CEAN"
    b= Split (a , """" , -1,1)

    it gives output as
    "Crazy
    BOY
    CEAN"
  • Saandeep Sreerambatla
    Saandeep Sreerambatla
    Yes both the opening quotes are closed.
    But , the first word crazy is given in quotes and cean is given in quotes where as the boy is made as a variable here.

    when you give input in such a way and execute it you will get "This test cannot run due to a syntax error , expected end of statement"
    You have to give boy also in quotes.

    And if you split as i mentioned you get Crazy Boy Cean only as i just saw it.
    It gives in that way because we are splitting using """" and more over the first " before Crazy is a quote as understood by VB so it will not consider that one.
    let me know if i answered your question.
  • ms_cs
    ms_cs
    shalini_goel14
    .....
    .....
    String u2=" ' "; //read the string at runtime
    ResultSet rs=stmt.executeQuery("select * from dbase where u_name='great' and pword='"+u2+"'''");
    .....
    
    Man, what you are trying to do exactly, can you Please explain?

    After substituting u2 in sql statement , your query would look something like below to sql engine
    select * from dbase where u_name='great' and pword='''''
    
    Right? I couldn't get your idea of putting so many useless strings. How can it help you out in sql injection attack? It's quite obvious you will get that exception.

    That statement should like this,
    ResultSet rs=stmt.executeQuery("select * from dbase where u_name='great' and pword='"+u2+"'");
  • shalini_goel14
    shalini_goel14
    ms_cs
    That statement should like this,
    ResultSet rs=stmt.executeQuery("select * from dbase where u_name='great' and pword='"+u2+"'");
    Man, then how it would make one condition in where clause true hmm?😕 You should use atleast something in u2 that can make one statement true so as the sql injection attack can be invoked. I am still not clear about what you are trying to ask or do? As per you statement would look like following to sql engine.

    select * from dbase where u_name='great' and pword='''
    
    I don't know how can you expect such statements to get executed by sql engine.
  • ms_cs
    ms_cs
    the table dbase contains the following entries,

    great person
    user1 password1
    I dont know the mistake.so,correct the following statement and give correct statement to perform sql injection.
    ResultSet rs=stmt.executeQuery("select * from dbase where u_name='great' and pword='"+u2+"'");
    and u2 is a string, Shall I give input as ' ?
  • ms_cs
    ms_cs
    Does it only applicable to sql ,or also applicable to DB2 and oracle too?

    I am asking this because, they also use SQL syntaxes mostly. If anything wrong means correct my mistake.

    Thanks
  • shalini_goel14
    shalini_goel14
    ms_cs
    Does it only applicable to sql ,or also applicable to DB2 and oracle too?

    I am asking this because, they also use SQL syntaxes mostly. If anything wrong means correct my mistake.

    Thanks
    See , I don't know much about DB2 but see if you got that error it means your query syntax is wrong and yes those rules apply to queries written in sql syntax only.

    PS: Anyone can correct me if wrong.
  • ms_cs
    ms_cs
    the table dbase contains the following entries,

    great person
    user1 password1
    I dont know the mistake.so,correct the following statement and give correct statement to perform sql injection.
    ResultSet rs=stmt.executeQuery("select * from dbase where u_name='great' and pword='"+u2+"'");
    and u2 is a string, Shall I give input as ' ?

    At runtime if I give input for u2 as ' then it shows error. Why?
  • ms_cs
    ms_cs
    My problem solved..I have given the input like this, a' or 'b'='b
  • frankiben123
    frankiben123
    wow thanks for the info.....
    #-Link-Snipped-#​

You are reading an archived discussion.

Related Posts

Hi CEans, Nice to introduce you the DEFORM software, the advanced tool to simulate materials forming, machining, heat treating process. This introduction is written mainly based on the presentation on...
CEans, As CE gains popularity and our traffic surges, we become a favorite spot for the ad - spammers. Our moderator team works round the clock to keep CE clean...
Puzzle; What is the missing nomber ? Admin note: No tricks, my dear friend.
Hi guys... i need some major help.....I am carrying out this design using the Actel ProAsic3 FPGA. can you guysplease help me to implement a scrolling message display ....the words...
Finally! Sprint has officially announced that the Palm Pre will be launching on June 6th for $200 after a $100 MIR and a two-year contract. You'll be able to purchase...