SQL Injection and Prepared Statements

My teacher and I are discussing whether SQL injection into a prepared statement is possible. I understand that normally you couldn't, but the professor insists on using sql concatenation instead of using (?).

Now I am trying to break my code, but I have no luck.

public Users getUserByUsername(String username) throws SQLException {
    StringBuffer sql = new StringBuffer();

    sql.append("select * from users as  u, user_type_lookup as l, user_types as t ");
    sql.append("where u.users_id=l.user_id and l.type_id=t.user_types_id and u.username='");
    sql.append(username);
    sql.append("';");

    System.out.println(sql.toString());

    PreparedStatement ps = conn.prepareStatement(sql.toString());
    ResultSet rs = ps.executeQuery(sql.toString());

    if (!rs.next()) {
        return null;
    }

    String password = rs.getString("password");
    String type = rs.getString("description");
    int id = rs.getInt("users_id");
    int incorrect_logins = rs.getInt("incorrect_logins");
    Time wait_time = rs.getTime("wait_time");

    Users u = new Users(id, username, password, type, incorrect_logins,
            wait_time);
    return u;
}

      

The inserts I've tried:

string: '; DELETE FROM users WHERE 1 or users_id = '
string: ';delete from users where username<>'
//The only one that worked    
string: stan' or 'a'<>'b

      

SQL output (results in java error):

select * from users as  u, user_type_lookup as l, user_types as t where u.users_id=l.user_id and l.type_id=t.user_types_id and u.username=''; DELETE FROM users WHERE 1 or users_id = '';

      

SQL output (works as intended):

select * from users as  u, user_type_lookup as l, user_types as t where u.users_id=l.user_id and l.type_id=t.user_types_id and u.username='stan';

      

Error message:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your    
SQL syntax; check the manual that corresponds to your MySQL server version for the   
right syntax to use near 'DELETE FROM users WHERE 1 or users_id = ''' at line 1

      

Server: Tomcat 7

Database: MySQL

IDE: Eclipse

Language: Java

So please help me to break my code!

+3


source to share


1 answer


You cannot add a separate statement inside the SQL prepared statement, but it can be broken down like:



  • using ' OR 'x' = 'x

    as the username (so the query will perform a Cartesian join between all users and display types between them); it will hurt performance a lot if users

    they user_type_lookup

    are large tables and would be a great start to a denial of service attack.
  • using ' OR (SELECT stored_procedure_that_deletes_things()) = 1

    (so the query will call the stored procedure, which has harmful effects).
+4


source







All Articles