SQL injections are known to be one of the most dreaded web attacks on enterprise databases, which is used for retrieving the most sensitive data from DBs. When you hear the news about stolen credit card details of passwords, it is often done with SQL injection attacks by leveraging the database vulnerabilities. However, being aware of its nature and function, there are many fixes that will help protect your web applications from possible SQL injections.
About SQL injection
SQL injection is a hacking technique that the cyber attackers execute by inserting a simple SQL query to the input fields which will then be processed by the backend SQL database. These can be achieved by exploiting the vulnerability that the user entry forms will also the user-generated SQL statements to query the underlying database directly.
For example, you may take a standard login form, which consists of user fields like entering the user email and the password. After submitting this info, it is combined with an underlying SQL query on the server. Using PHP, such a command may be written somewhat as below:
This will be sent to the DB servers to verify that the given username and password are valid. For example, a username as “kate” and password as “1234” may result in the following command.
In such a user-generated form scenario, the attackers may be using the SQL comment syntax. Remaining codes after the sequence of double-dash “—”may not run. This means the SQL query would look like:
This will return user data, which gets entered in the field for password. This move will help the intruders to bypass the login screen. The attackers may also go a further step by adding a select condition also as ‘OR 1=1’, which will, in turn, make the following query.
This SQL query will return the nonempty dataset to the potential logins with all the user tables in the database. This is a significant security flaw that may be out there in any random site, if not taken care of. This is a straightforward and primary example of what a hacker can do. There are advanced methods that the hackers use to run arbitrary statements too with SQL queries, which will cause collateral damage. These attacks may lead to data security threats like:
- Extracting private data from databases like credit card information, patient healthcare records, passport details, financial data, etc.
- Enumeration of user details for authentication, which will let individual logins to be used on other websites to gain access.
- Database corruption and execution of OS commands.
- Insert or deleted data and also destroy operations of the entire website.
- Compromising full system.
Preventing SQL injection
As the significant vector for SQL injection attacks are input channels accessible to users, the best possible approach is to control the vet the user inputs to keep a watch on the attack patterns. The developers can avoid such vulnerabilities by taking the following preventative measures.
Validation of input data
The validation process is meant to verify whether the type of through the given field is allowed as preset. Input validation will ensure that the characters entered in such fields are of accepted type, format, length, etc. Only the entries which pass this validation criterion will be sent to the application layer. This will help to counteract any malicious commands intentionally or unintentionally inserted into an input field. This way, it is just as looking who is there outside before opening the door on a knock.
As RemoteDBA.comreiterates, validation has to be applied to all the fields which let the users to input data, which means you should also take care of various situations and take appropriate measures.
- Always use the regular expressions as the whitelists in case of structured data like name, genera, age, income, zip code, survey response, etc., which will ensure foolproof input validation.
- In case of any fixed value sets as a list with dropdown entries, radio button selection, etc., the developer may custom define which values are returned. Anything apart from what exactly matches the offered options will be rejected.
Let us explore how the table name validation can be executed.
In this string, the variable of $tableName can be appended directly. This is now accepted as the legally accepted universal value for a database table name.
In dropdown lists entry, it is much easier to do data validation. By assuming that you want the users to choose a rating among 1 to 5 from the options, change the PHP code as below.7
In this, there are two basic checks as:
– It should be a number – (is_numeric ()).
– You need the number to be more than 0 and less than 6, which leaves only with the range of 1-5 for validating.
All such data coming from an external party needs to be validated to mitigate the SQL injection threats. This rule will apply not only to the input from internet users, but can also be extended to the internal users, suppliers, partners, regulators, and vendors, etc. Sometimes, even the external vendors may be under a cyberattack and can send malicious data even unintentionally.
Stored procedures will make the developers to group one or multiple SQL statements arranged into one logical unit with a customized execution plan. The subsequent executions will let the statements to parameterized automatically. It is a type of code that can be stored for later and used repeatedly. Whenever you execute this query, rather than requesting writes, again, and again, it will just call the stored procedure once.
Let us see the process for the creation of a stored in the SQL server. Take an example that your table looks like below.
In the above query, supposed if an employee needs to get aggregated data on salary from the given table, you have to create a user for it as ‘tr’:
Uses need the EXECUTE privilege to access the schema by reaching to where the corresponding table is located.
Along with the above, there are many other prevention methods also to tackle the SQL injection as parameterized queries, input validation, stored procedures, and escaping etc. However, if you want to cover the entire bases, you should try the above precautions along with a trusted WAF. The benefit of using a WAF is that it will offer protection for the custom web apps too, which go unprotected otherwise.