Protection and Prevention
Now that you have been introduced to the methods by which a hacker can gain access to your SQL server, we will discuss how a SQL server can be secured and show you how to program scripts that are less vulnerable to SQL injection attacks.
The first thing that should be done in all database servers is to assign a strong password to the DBA account. Second, the DBA should create user accounts and assign these users to specific activities or databases. For example, if a user account has been set up for the Users database, which didn't have access to the Master database, any attempt at using extended stored procedures (such as xp_cmdshell) will not work.
Next, any user-entered variable should be stripped of several key characters that are required for SQL injection. This includes the following:
" , / \ * & ( ) $ % ^ @ ~ ´ ?
This could include, more depending on the needs of the script. By adding the following to the previously listed script, any attempt at SQL injection would be made impossible:
username = replace (username, ', '')
username = replace (username, ";", "")
This would remove all single quotes and semicolons from the query string and would have turned the inject string into the following:
"seth update tblusers set password=hacker where username=seth"
This string would have been interpreted as garbage by the SQL server and would have been rejected, thus stopping the SQL injection attack.
Next, ensure that all accounts have strong passwords. There is no excuse for a DBA account to have a blank password. Even if the server is for testing purposes, if a hacker can access that server, he can also access any other computer connected to the SQL server's local network.
Finally, some changes can be made to the registry and SQL server configurations that will tighten security by removing or limiting extended procedures and other rarely used functions. This includes removing the xp_cmdshell stored procedure, or at least renaming it. In addition, you can use REGEDIT to adjust the value of the following to 1:
HKEY_LOCAL_MACHINES\Software\Microsoft\Microsoft SQL Server\ <Instance Name>\Providers\DisallowAdhocAccess to 1 Or if using the default, HKEY_LOCAL_MACHINES\Software\Microsoft\MSSQLServer\ MSSQL\DisAllowAdhocAccess
This disables all OLE DB ad-hoc queries from the SQL server.