How to secure SQL Server from SQL injection attacks

SQL injection attacks can be the most common way for hackers to attack an Internet-facing SQL Server database. Any application that uses dynamic SQL, allowing untested user input to submit to the database, is at risk of SQL injection attacks, regardless of how secure your network is or how many firewalls you have installed. A recent report on Web hacking has shown that SQL injection attacks are on the rise, not just in data theft and data loss, but also in the recent series of automatic injection attacks that have corrupted the database and provided users with malicious Java scripts. This infiltration causes the Web server to infect other viruses on the client computer. The report does not count for the number of sites that have been attacked, but even the lowest numbers are tens of thousands. At the peak of the infection, even the United Nations web site was not spared.

You might think that if the SQL Server platform is so insecure, consider replacing the platform. But in fact, all of the database platforms are plagued by this attack. Because SQL Server is more deployed in the host environment, attacks against SQL Server are also more prevalent. Developers work on web development, and they don't know how to guard against such attacks. Because of the high success rate of this attack, it is quite popular in the malware community.

How does a SQL injection attack work?

Web applications that are vulnerable to SQL injection attacks have the following characteristics:

1. Your site uses dynamic SQL. This does not mean that the application dynamically generates a SELECT or INSERT statement. It means that any code is dynamically generated, including that the application dynamically generates a stored procedure before executing the statement.

2. When a value is taken from a client application, the values are not validated-or the syntax or escape characters may not be validated.

SQL injection attacks do this: An attacker modifies an existing command line by inserting a single quotation mark within a string value, or by adding a semicolon after the number, which is written to the SQL statement after the escape character. The command line looks like this:

exec sel_customerdata @CustomerId = 47663; Truncate TABLE Customer

This executes the Sel_customerdata procedure and then runs the TRUNCATE TABLE command to delete the contents of the customer datasheet. If there is a foreign key constraint on this datasheet, the database will return an error and provide the hacker with the constrained data table name. A clever hacker can use this technique to find the name of each table in the database. The hacker can then insert data into your database, or select data from your table (depending on what permissions the database gives the application). When hackers get data from a data table, they can use xp_sendmail or sp_send_dbmail to send e-mail to themselves. Even if you disable these processes, hackers can easily enable them or use the sp_OA process to add their own processes.

How do I ensure that SQL Server data is secure and that SQL injection is avoided?

There are a number of ways to protect your database against such attacks.

First, we need to use database security best practices to strengthen database security. This includes setting the operation license for the database to the lowest level (setting up the DB security with the lowest set of permissions possible.). Also, applications do not access data tables directly. All access to the datasheet should be done through stored procedures, and those stored procedures should not include any dynamic SQL.

Avoid direct access to the table, you can greatly reduce the level of attack. But that's not the only thing that has to be done. Stored procedures also have the possibility of being attacked. While it takes more time to attack a stored procedure, it is still possible to use your stored procedures to destroy the database. Stored procedures are used to insert, update, and delete data into the database. A smart hacker can use your own stored procedures to attack you.

This is an area where application developers and you work together to ensure that the code that is executed (the being executed against the database) is secure. Without ensuring the security of the application layer and preventing SQL injection attacks, the rest of the work will be futile. It is almost impossible to validate data in a database as long as it enters the database. This requires validation of the data at the application layer.