SQL Injection

    Introduction

    SQL Injection is one of the possible ways to crack web applications connecting with SQL database (DB). This method is based on malicious code injection into the initial SQL script. For example, a hacker can do the following on a compromised database server:

    • Get access to a table with restricted access.
    • Delete data from tables, delete tables themselves.
    • Modify/add data in tables.

    The developer of such software working with DB should take into account the potential vulnerability of his applications and apply measures to counteract SQL Injections.

    The core of SQL Injection.

    Let’s imagine that there is a Java web application working with user requests via HTTP. Implementation is not so important, indeed, an SQL code can be injected using an incorrectly designed user interface even in a console application. A part of this vulnerable application’s logic is described in the following example:

    String login = ...;
    String password = ...;
    String generatedSql = “SELECT * FROM users WHERE 
         (login = ‘“ + login +”’ and 
         password = ‘“ + password+ ”’)”;

    During the normal use of a server, it receives valid and logical data. For example, the following values:

    • login = my_login@mail.com
    • password = my_password_123

    In this case the Java strings concatenation will occur and the final SQL request to the DB will look like this:

    String generatedSql = “SELECT * FROM users WHERE 
         (login = ‘my_login@mail.com’ and 
         password = ‘my_password_123’)”;

    But if the server is attacked by an intruder, the following values can be sent to the server by the intruder instead of logical parameters:

    • login = my_login@mail.com
    • password = ‘ or 1=1)#

    Accordingly, an SQL request with an intruder code injection will be executed:

    String generatedSql = “SELECT * FROM users WHERE 
         (login = ‘my_login@mail.com’ and 
         password = ‘’ or 1=1#’)”;

    After executing this script, the DB server returns the correct response, despite the absence of the password in the required parameter. It is worth noting that the hash symbol at the end of SQL Injection is needed for screening (commenting) subsequent restrictions of the original SQL query in MySQL RDBMS.

    As mentioned earlier, an attacker can not only log in to the system, but also, for example, delete data and even tables. If the vulnerability occurs, nothing prevents the intruder from implementing the following SQL program instead of the parameter:

    Bob ‘); DROP TABLE contracts;#

    This will also cause huge damage to the attacked program.

    Using UNION in SQL Injection.

    SQL has the ability to use the word UNION and thus to combine multiple query results into a single table. This method also enables getting unauthorized access to data. Let’s imagine that there is an HTTP service that provides the user with news and accepts only news ID. Inside this service, there is the following SQL script that is responsible for displaying news:

    String newsId = ...;
    SELECT id_news, header, body, author 
         FROM news 
         WHERE id_news = " + newsId;

    In this case an attacker can inject the following SQL program as the newsId parameter:

    -1 UNION SELECT 1,username, password,1 FROM admin

    As a result, the final SQL query will look like this:

    SELECT id_news, header, body, author 
         FROM news WHERE id_news = -1 
    UNION SELECT 1, username, password, 1 
         FROM admin

    This fake request will return records related to the security of the service – a list of user names and passwords from the admin table and, at the same time, there will be no output of any records from the news table.

    SQL Injection in ORM Hibernate

    ORM systems such as Hibernate are also exposed to potential risk of SQL attacks. This is because Hibernate has the ability to operate in the HQL language (Hibernate Query Language). When creating an HQL query, a developer can make the error of using string concatenation, as shown in the example:

    SessionFactory sessionFactory = ...;
    String user = ...;
    String password = ...;
    String generatedHql = “FROM LoginInfo 
            WHERE userName = ”+ user +” AND password = “+ password +”;”;
    List<LoginInfo> logins = sessionFactory.
    	getCurrentSession().
    	createQuery(generatedHql).list();

    In this case, the HQL code is vulnerable because this example of Java code is constructed in a dangerous way and enables implementing custom HQL code.

    Ways of protection against SQL injection

    Protection through the use of PreparedStatement and Statement interfaces

    The Java Standard Library has PreparedStatement and Statement interfaces that can be used to protect against SQL injection.

    The main difference between PreparedStatement and Statement is that Statement interface does not accept any parameters and is applied if static SQL is used. Statement performance is low due to the fact that the execution of such queries is not cached.

    PreparedStatement is applied when it is planned to use SQL expressions many times with various parameters. For example, the corrected version of the above SQL query will look like this:

    String login = ...;
    String password = ...;
    String sqlTemplate= “SELECT * FROM users WHERE 
         (login = ? and password = ?)”;
    Connection connection = ...;
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1, login);
    statement.setString(2, password);

    It should be noted that the use of PreparedStatement is justified here both in terms of security and in terms of performance:

    • As for security, the idea here is that if the position of the parameters is explicitly set, then it is absolutely safe to transfer SQL queries to the database, and these parameters will not become SQL expressions (including malicious ones).
    • Performance increases because client applications do not need to parse this PreparedStatement again, if it has already been executed. The client application just inserts the necessary parameters into the body of the SQL query. PreparedStatement itself is a reusable template.

    Hibernate protection using parameterization

    Vulnerabilities in HQL can be prevented by applying named parameters instead of Java String concatenation. Such a possibility is integrated in ORM Hibernate:

    SessionFactory sessionFactory = ...;
    String user = ...;
    String password = ...;
    // Modified HQL query:
    String generatedHql = “FROM LoginInfo 
            WHERE userName = :name AND password = :password”;
    // Setting HQL parameters:
    query.setParameter("name", user);
    query.setParameter("password", password);
    List<LoginInfo> logins = sessionFactory.
    	getCurrentSession().
    	createQuery(generatedHql).list();

    Use of a sqlmap analyzer

    Sqlmap can be used as an automated approach to SQL Injection search in the software. This tool is a part of Kali Linux distribution and can be installed on Windows (Python is required additionally).
    It has the following features:

    • Support of various RDBMS implementations: Oracle, MySQL, PostgreSQL, MSSQL, IBM DB2 and others.
    • Support of many types of SQL Injection, including boolean-based blind, time-based blind, error-based, UNION query, and stacked queries.
    • Support of direct connection to RDBMS.
    • Operations on a specific URL.
    • Support of listing user names, password hashes, access rights, roles, etc.
    • Many other features.

    To initiate verification for vulnerabilities, it is necessary to launch sqlmap from the console for scanning:

    >python sqlmap.py -u localhost/test/

    While operating, sqlmap will ask various questions, such as whether it is necessary to try to determine the name of the Web Application Firewall or even try to check the service for XSS (Cross-Site Scripting – when a malicious code is downloaded along with a Web page). Program operation results, for example, SQL Injections identified, will be displayed in the application console.

    Conclusion

    In this article, we have covered the essence of SQL Injection and considered some common ways to protect against SQL Injection that you can use:

    • Using tempated, cached PreparedStatement, preventing insertion of third-party custom SQL programs in server code.
    • Using named parameters in HQL queries instead of Java string concatenation.
    • Using the vulnerability search automation using sqlmap as an example.

    Links used

    https://habr.com/post/148151/ (in Russian)
    https://en.wikipedia.org/wiki/SQL_injection
    https://xakep.ru/2011/12/06/57950/ (in Russian)
    http://sqlmap.org/
    https://kali.tools/?p=816