SQL Injection

SQL Injection :

SQL Injection is the attack caused by the malacious code inserted in string which is passed as parameter to the sql statements.SQL Server will execute all syntactically valid statements with the parameters passed to it.All SQL statements should be reviewed for injection vulnerabilities because parameterized data can be manipulated by a skilled and determined attacker.

SQL injection is either direct insertion of code into user-input variables that are combined with SQL commands and executed or injecting malicious code into strings that are used for storing in a table or as metadata.

Consider one simple example :

User login form:

<form method="post" action="http://jagadishmm.blospot.com/login.jsp">
<input name="txtfname" type="text" >
<input name="txtPassword" type="password" >
</form>

Validating user:

    SELECT userid FROM users WHERE uname = "+txtfname+" AND password = "+txtPassword+";

suppose the parameter passed to the query is modified in the following way,

    SELECT userid FROM users WHERE uname = 'test';drop table users--' AND password = "test123";

How to avoid SQL Injection:

    1.Test the size and data type of input and enforce appropriate limits
    2.Validate input in the user interface for special characters ,escape sequence..viz ";","--","'","/* ... */"..etc.
    3.If you are working with XML documents, validate all data against its schema as it is entered
    4.Never build SQL statements directly from user inputs
    5.Its better to use stored procedures to validate user input
    6.use bound parameters(Prepare statements) :

    for example :

    Insecured way :

    Statement s = connection.createStatement();
    ResultSet rs = s.executeQuery("SELECT userid FROM users WHERE name = "+ txtName);

    Secured way :

    PreparedStatement ps = connection.prepareStatement("SELECT userid FROM users WHERE name = ?");
    ps.setString(1, txtName);
    ResultSet rs = ps.executeQuery();


    6.use stored procedures.

Note that :

    1. The semicolon (;) denotes the end of one query and the start of another
    2. The double hyphen (--) indicates that the rest of the current line is a comment
    3. /* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.