Database technology has greatly improved the way we handle large amounts of data, and almost every modern application uses it in one way or another. However, the widespread use of databases leads to a number of security holes and attacks. SQL injection has been around for some time, and so there are many defenses against these types of attacks.
User Input Escaping
The first line of defense that is set up by developers is often the user entering escaped. We've seen how certain characters such as string terminators (& # 39;) and comment markers (-) can be abused if certain input fields allow it. If all user-supplied input is correctly masked before the SQL query is even executed on the database, in most cases, no input errors will occur.
The scheme for character bypass depends on the type of database system used. For example, MySQL has two main modes for bypassing user input: the first one, which replaces every single quotation mark with two single quotation marks, and a second one that uses a backslash for special characters:
ESCAPE SEQUENCE CHARACTER, REPRESENTED BY SEQUENCE --------------- --------------------------------- 0 An ASCII NULL (X & # 39; 00 & # 39;) character & # 39; A single quote (& # 39;) & # 39; & # 39; A double quote (& # 39; & # 39;) character b A backspace character n A newline character (linefeed) A carriage return t A tab character z ASCII 26 (Control + Z) \ a backslash () % A character _ An underscore character (_)
However, as described in the previous tutorial on avoiding detection and avoidance of defense mechanisms, there are certain techniques that can be used to circumvent this type of security. To reduce the likelihood of these types of attacks being successful, you need to use safer methods against SQL injection.
Whitelist input validation
A common mistake attempting to validate user input is to deny blacklist, individual characters, and expressions from submission to the database. This is a flawed approach, since it is trivial for a determined attacker to bypass these defenses. A more appropriate approach is to use whitelisting as input validation.
Whitelist input validation defines exactly what may be sent to the database. Accordingly, anything that is not explicitly stated is not authorized by default. Consistently structured data such as data, phone numbers, addresses and e-mail addresses can be easily validated against specific patterns, often using regular expressions.
Whitelisting can be an effective deterrent to SQL injection, especially if used together with other methods of defense, but it is still not bulletproof. One of the major drawbacks is that all possible allowed data must be known and taken into account in validation. This is especially difficult in dynamic environments or when codebases merge and diverge. It depends on the separation of code and data and, if possible, standardized validation techniques should be used.
Stored procedures are piles of SQL statements that will later be murdered in the system as objects stored in the database system. These are especially useful for developers because they make it easier to centralize and standardize groups of queries that are frequently executed – think that they are similar to the functions or methods used in other programming languages. Benefits of using stored procedures include proper encapsulation of data, internal error handling, reduction of network traffic, and the concept of write-once, reading many.
If stored procedures are implemented properly, they are generally considered fairly secure. In certain situations, eg. For example, if SQL code must be dynamically generated within a stored procedure, security vulnerabilities can occur. If absolutely unavoidable, it is imperative that other input validations or escapes be performed concurrently.
In some cases, using stored procedures may be more of a security benefit than a benefit. While many SQL queries only work with read and write permissions, stored procedures require execute privileges. Therefore, it is theoretically possible for an attacker to fully own a compromised database server when using stored procedures.
The best way to prevent SQL injections is to use prepared statements. Prepared statements use parameterized queries to predefine all of the SQL code, and then pass each parameter to the query. This ensures the integrity of the statement, making it extremely difficult for a attacker to change the code. Imagine ready-made instructions similar to variables in other programming languages; The variable is set and later passed to a function when it is called.
Prepared statements are probably the best way to differentiate between code and data used in SQL statements. Almost every programming language used to build web applications allows the use of parameterized queries. Here is an example of how this might look in PHP and Java:
PHP EXAMPLE ----------- $ stmt = $ dbh-> prepare ("UPDATE (name, value) VALUES (: name, value)"); $ stmt-> bindParam (& # 39 ;: name & # 39 ;, $ name); $ stmt-> bindParam (& # 39 ;: value & # 39 ;, $ value); JAVA EXAMPLE ------------ String username = request.getParameter ("username"); String query = "SELECT address FROM user_info WHERE id =?"; PreparedStatement pstmt = connection.prepareStatement (query); pstmt.setString (1, username); ResultSet results = pstmt.executeQuery ();
Like many other situations encountered in penetration testing and hacking, it often happens that a system has a system: privilege. The principle of least privilege requires that each component of a system, be it a user, program or process, be granted access only to the minimum amount of resources and information necessary to fulfill its purpose. This means that normal users are not given special access, such as superuser or administrative permissions. This also means that applications and processes running in an environment will only have access to the functions they need for their function.
This principle applies to database systems to minimize the damage of an SQL injection attack. It's easy for DBAs and developers to do everything with full administrative privileges, because often everything works better, but this can open up major security holes for attackers. If an account needs only read access to specific tables, it is important to restrict access in this way. If an account only needs access to specific parts of a table, consider using views to limit what the account can see. It's also a good idea to have separate database users for different web apps – this reduces the risk of a compromised account accessing data from other web applications.
This series on SQL injection has the basics of databases and SQL, exploration techniques, advanced attacks, signature avoidance, and common defense methods in the industry. It is important for you, as a white hat, to understand a technology from scratch, and now that we have dealt with the SQL injection in depth, you can continue and truly be a master of this craft.
Do not Miss: Zero Byte's Guides to SQL Injection