How to Prevent SQL Injection In SQL Server

Overview

The MS SQL Server uses a default administrator account in its every edition. The database administrator creates an administrator account and manage the database using SQL Server authentications. If any application of MS SQL Server installed on the user’s system, it is highly recommended to set a default password for the database as well to prevent SQL injection in SQL Server, from which the database administrator can utilize other functionalities of the database. There is a list of some common database applications and their default administrator accounts:

The usernames/passwords are so simple and can be gotten from any web site. For this reason, one of the first tasks of the database administrator is to configure the SQL Server and set up a strong password for the database. However, unfortunately, often it is ignored or postponed by the database administrator until it is forgotten.

In addition, hackers can attack on this Server connected to the internet and completely own this database.

Sometimes, SQL Server administrators use easy and small length passwords to make instant SQL Server logins. Therefore, it becomes easy to identify the administrator password for the hackers.

There is another technique, often used by hackers is SQL injection. In this article, you will know about SQL injection and techniques to prevent SQL injection in SQL Server.

What is SQL injection?

SQL injection is a query used by hackers to steal the information from the database. It is a command of SQL which directly attacks on the database or on SA passwords.

Mainly the SQL Injection is a hacking technique, in which the hackers try to pass some malicious SQL statements from a web application for completion by the backend database. If the application is not properly sanitized, it might get injected by hackers to view data from the database or even clean it out.

Sometimes hackers pass the fake login forms and identify what is behind it. This is only possible if the user inputs are not properly clean and sent directly through the SQL statements to the database.

How SQL injection works?

In order to prevent SQL injection in SQL Server, we should know how it works. The SQL injection works when some malicious SQL queries inserted by the hackers in a web application form. The working process is similar to the client data submission through a web form. Intruders attack through malicious SQL statements into the input fields and modify SQL query that was intended to work, and helps them to gain the unauthorized data or damaging the database. Well the text data, which we used as an input through the text boxes in the web form, becomes integral part of the SQL query executed at the database server

What is impact of SQL injection?

Once the hacker recognizes that a system is fully applicable of SQL Injection, he applies SQL injection techniques like query,commands or an input form field. This is just like that attacker is handling your database and permitting him to run SQL statements including DROP database.

An attacker may attack any arbitrary SQL statements on the vulnerable system. This may cooperate to the integrity of user databases and disclose the sensitive information.

Example:

if an application has a login field where the user enters the username and the field is unlocked for injection, then an intruder can enter query something like:
MyName ‘ ;DROP TABLE Users;–

In the above example, the attacker finishes the query with the closing single quote and semicolon, adds another query and then comment anything subsequently, the database may run both the queries. So, this raises a question how to prevent SQL Injection attack in SQL Server. Before coming to this let’s know how you can detect SQL Injection.

SQL Injection Detection

The SQL injection is tough to detect from the SQL Server side. If a user wants to find out who modified what and when in the database, then some type of tracing must be run on the server.

If no tracing is running, then you can see the following events:

• Windows event logs help in tracking all the events on Windows
• SQL Server error logs show all the failed attempts
• Default Trace for objects modified.
• Successful and Failed logins
• View SQL Server transaction logs

It is very important to check the security part of the database, which is open to hack.

The SQL Server Auditing feature is very helpful to track SQL injection. This method produces the least amount of impact on the performance of SQL Server as compared to SQL Server trace technology. However, The performance issues are totally depending on the database traffic. Therefore, it is very important to choose the appropriate SQL Server Audit Action Type options. This will help to detect SQL Injection in SQL Server. Now, after detecting we should know SQL Injection prevention techniques in SQL Server.

How to Prevent SQL Injection In SQL Server?

The best way to prevent SQL injection in SQL Server is to use stored procedures.

userInput = "'; Drop Table Test; --"
 
conn = pyodbc.connect(connString)
curs = conn.cursor()
 
sql = "Select City, State from dbo.ZipCodes where zipcode = ?"
 
curs.execute(sql, (userInput,))
conn.commit()

Through this the profiler receive quite a few messages, but basic part is:

exec sp_prepexec @p1 output,N’@P1 varchar(22)’,N’Select City, State from dbo.ZipCodes where zipcode = @P1′,”’; Drop Table Test; –‘

Since it Accepts the injected code as a variable, the SQL Server returns an empty result set after looking for the value in the table. So the injected string is never effected and the table is never dropped.

Conclusion

The SQL injection is a very effective and common method, usually used by hackers to steal the data from user’s database. If the user does not allow any firewalls in security part, the hackers can easily break the security of the database through SQL injection. Using stored procedure in the script is an easy way to prevent SQL injection in SQL Server. Nevertheless, there are some undocumented functions like fn_dblog() and fn_dump_dblog() which can be helpful for Reading SQL Server Transaction logs.