SQL injections are one of the greatest risk on the web today. It is not difficult to rebuild existing programs so that SQL injections are no longer possible. The main problem of most programmers is lack of knowledge about this type of attack. Understanding it and identify risks in your applications is absolutely critical.
A SQL injection is nothing more than the manipulation of the SQL command on the victim’s side. It is the exploitation of a vulnerability in connection with SQL databases. The attacker attempts to infiltrate its own database commands on the application. Its aim is to spy or change data, to gain control of the server, or simply to inflict maximum damage .
Let’s look the following naive approach how a login is implemented. The email and password is replaced with the data entered in the SQL string. The so generated SQL statement is then sent to the database. If a value is returned, the user is considered as logged in.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| String email = request.getParameter("email"); | |
| String password = request.getParameter("password"); | |
| String sql = "select * from users where (email ='" + email +"' and password ='" + password + "')"; | |
| Connection connection = pool.getConnection(); | |
| Statement statement = connection.createStatement(); | |
| ResultSet result = statement.executeQuery(sql); | |
| if (result.next()) { | |
| loggedIn = true; | |
| // # Successfully logged in and redirect to user's profile page | |
| } else { | |
| // # Auth failure – Redirect to Login Page | |
| } |
A SQL injection can be made quite easily. We only need to inject characters interpreted by the database in order that the SQL string is modified. If ‚ or 1=1)- – is entered as password, the generated SQL look as follows:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select * from users where (email ='' and password ='' or 1=1)–') |
The SQL above is a completely valid statement. It will return all rows from the table users, since 1=1 is always true. The — is a comment and causes that everything behind is ignored. A second possibility to start a SQL injection attack is to enter ‚ or “=“ as email and as password. The generated SQL looks then as follows:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select * from users where (email ='' or ''='' and password ='' or ''=''); |
This statement will also return all users since “=“ is always true. In this manner one get access to all the user names and passwords in a database.
But how can SQL injections be avoided? An application can be protected from SQL injection attacks by using SQL parameters. For this purpose, so-called prepared statements are used in Java, that means the data is passed as a parameter to an already compiled statement. The data is therefore not interpreted and thus prevents SQL injection attacks. The changed code looks then as follows:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| … | |
| String sql = "select * from users where (email =? and password =?)"; | |
| Connection connection = pool.getConnection(); | |
| PreparedStatement pstmt = connection.prepareStatement(sql); | |
| pstmt.setString(1, email); | |
| pstmt.setString(2, password); | |
| ResultSet result = pstmt.executeQuery(); | |
| … |
The ? is used as a placeholder. By using the PreparedStatement class, the program may even experience a gain in performance if the statement is used multiple times.
René Winkler 