Using Parameterized Query to Avoid SQL Injection

introduction

to explain why you have to use parameterized query to avoid sql injection over concatenated inline query it needs to know about sql injection.

what does sql injection mean?

it means when any end user send some invalid inputs to perform any crud operation or forcibly execute the wrong query into the database, those can be harmful for the database.

harmful means ‘data loss’ or ‘get the data with invalid inputs.

to know more, follow the below steps.

step 1: create a table named ‘login’ in any database.

    create table user_login  
    (  
       userid varchar(20),  
       pwd varchar(20)  
    )  

now save some user credentials into the database for login purpose and select the table.

    insert into user_login values('rahul','bansal@123')  
    insert into user_login values('bansal','rahul@123')  

step 2: create a website named ‘website1’.

now i will create a login page named ‘default.aspx’ to validate the credentials from the ‘login’ table and if user is valid then redirect to it to the next page named ‘home.aspx’.

add 2 textboxes for userid & password respectively and a button for login.



add 2 namespaces in the .cs file of the ‘default.aspx’.

    using system.data.sqlclient;  
    using system.data;  

now add the following code to validate the credentials from the database on click event of login button.

    protected void btn_login_click(object sender, eventargs e)   
    {  
        string constr = system.configuration.configurationmanager.connectionstrings["constr"].connectionstring;  
        sqlconnection con = new sqlconnection(constr);  
        string sql = "select count(userid) from user_login where userid='" + txtuserid.text + "' and pwd='" + txtpwd.text + "'";  
        sqlcommand cmd = new sqlcommand(sql, con);  
        con.open();  
        object res = cmd.executescalar();  
        con.close();  
        if (convert.toint32(res) > 0) response.redirect("home.aspx");  
        else   
        {  
            response.write("invalid credentials");  
            return;  
        }  
    }  

add a new page named ‘home.aspx’.



where any valid user will get welcome message.

step 3: now run the ‘default’ page and log in with valid credentials.

it will redirect to next page ‘home.aspx’ for valid user.



note:

1. here i have not used the textmode="password" property in password textbox to show the password.
   
2. i have not used any input validations to explain my example.

problem: now i will perform the sql injection with some invalid credentials with successful query execution and after that i will redirect to the next page ‘home.aspx’ as a valid user.

i will enter a string in both textboxes like the following:

‘ or ‘1’=’1

now run the page and login with above string in both textboxes.



it will redirect to next page name ‘home.aspx’ for valid user.

see what happened. this is called sql injection in the hacking world.

reason:

it happened just because of the string and after filling this string in both textboxes orur sql query became like the following:

select count(userid) from user_login where userid='' or '1'='1' and pwd='' or '1'='1'

which will give the userid count and that is 2 in the table because 2 users are in ‘user_login’ table.

it can be used in more ways like just fill the following string only in user id textbox and you will go the next page as valid user.

or 1=1 - -

and it will also give users count 2 because sqlquery will become like the following:

select count(userid) from user_login where userid='' or 1=1 --' and pwd='' or '1'='1'

note: the sign -- are for commenting the preceding text in sql.

it can be more harmful or dangerous when the invalid user/hacker executes a script to drop all tables in the database or drop whole database.

solution:

to resolve this issue you have to do 2 things:

1. always use parameterized query.
2. input validations on client and server both side.

sometimes if your input validation fail, then parameterized will not execute any scripted value.

let’s see the example.

    protected void btn_login_click(object sender, eventargs e)   
    {  
        string constr = system.configuration.configurationmanager.connectionstrings["constr"].connectionstring;  
        sqlconnection con = new sqlconnection(constr);  
        string sql = "select count(userid) from user_login where userid=@userid and pwd=@pwd";  
        sqlcommand cmd = new sqlcommand(sql, con);  
        sqlparameter[] param = new sqlparameter[2];  
        param[0] = new sqlparameter("@userid", txtuserid.text);  
        param[1] = new sqlparameter("@pwd", txtpwd.text);  
        cmd.parameters.add(param[0]);  
        cmd.parameters.add(param[1]);  
        con.open();  
        object res = cmd.executescalar();  
        con.close();  
        if (convert.toint32(res) > 0) response.redirect("home.aspx");  
        else   
        {  
            response.write("invalid credentials");  
            return;  
        }  
    }  

now if i run the page and try to login with sql scripts as done earlier.

with ‘ or ‘1’=’1



with ' or 1=1 - -



as you have seen parameterized didn’t execute the sql script but why?

reason: the reason behind this the parameterized query would not be vulnerable and would instead look for a user id or password which literally matched the entire string.

in other words ‘the sql engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the sql to be executed’.

conclusion: always use parameterized query and input validations on client and server both side.