SQL Server encryption and SQL Injection

Encryption on SQL Server

SQL Server has built-in encryption to protect various types of sensitive data. In many cases, this encryption is completely transparent to you. When data is stored, it is encrypted and used automatically. In other cases, you can choose whether the data is encrypted. SQL Server can encrypt the following components:
· Password
· Stored procedures, views, triggers, user-defined functions, default values, and rules.
· Data transmitted between servers and users
Password Encryption
SQL Server automatically encrypts the passwords you assign to login and application roles. Although you can view the system table directly from the primary database without the password. You cannot make any changes to this situation. In fact, you cannot destroy it at all.
Define Encryption
In some cases, if the object is encrypted, it is prevented from sharing some information to others. For example, a stored process may contain the owner's business information, but this information cannot be seen by others, even if they publish a system table and can see the definition of objects. This is why SQL Server allows you to encrypt when creating an object. To encrypt a stored process, use the following creat procedure statement:
Create procedure procedurename [; number]
[@ Parameter datatype
[Varying] [= defaultvalue] [Output]
[,…]
[With recompile | encryption | recompile, encryption]

We only care about the optional with parameter. You can describe arecompile or encryption in detail, or you can describe them at the same time. The encryption keyword protects SQL Server from being exposed in the process. As a result, if encryption is activated, the system storage process sp_helptext will be ignored, and the storage process will be stored in the text of the process created by the user. If you do not want encryption, you can use alter procedure to ignore the with encryption clause to recreate a process.
To enable encryption. Both users and servers should use TCP/IP networklibraries for connection. Run the appropriate network utility and check force protocol encryption. As shown in the following table, the connection between the user and the server will not be encrypted.

Encryption cannot be completely free. After the connection is confirmed, other constructor will be continued, and the user and server must run code to interpret the encrypted and interpreted packages. Some overhead will be required here and the process will slow down when decoding. If the network package is out of your control, this is a good practice.

What is missing in encryption?
You can notice that there is something in this list that is encrypted: Data in your table. Before you store data, SQL server does not provide any built-in tools to encrypt your data. If you need to protect data stored on SQL Server, we will give you two suggestions: first, you can use the grant and deny keywords to control the data that you want to read in SQL Server.

2. If you really want to encrypt data, do not try to add a password. You can use the algorithms of tested commercial products.

SQL injection attacks
SQL injection is a conventional attack that allows some unscrupulous users to retrieve your data, change server settings, or blacklist your server when you are not careful. SQL injection attacks are not SQL server problems, but inappropriate programs. If you want to run these programs, you must understand that this is at risk.

Measurement point positioning weakness
The weakness of SQL Injection occurs when the program developer constructs a where clause along with user input. For example, a simple ASP program allows users to enter a customer ID and then retrieve the names of all employees of the company. If the customer ID is returned as part of the request string of the ASP page, developers can write the following code to obtain data:

Strconn = "provider = sqloledb; Data Source = (local );"&_
"Database = northwind; Integrated Security = sspi"
Set CNN = server. Createobject ("ADODB. Connection ")
CNN. Open strconn
Strquery = "select contactname from MERs "&_
"Where customerid = '" & request. Form ("custid ")&"'"
Set rstresults = CNN. Execute (strquery)
Response. Write (rstresults. Fields ("contactname"). value)
 
Do you know where the problem is? If you know a user's ID, you can retrieve all the corresponding names. Understand now?

Obtain additional data
Of course, for an attack program, even though it does not know the ID of any customer or even does not have to guess, it can also obtain data. To do this, it enters the following text into the textbox of the customer ID called by the application:

Customer ID:
'Union all select contactname from MERs
Where customerid <>'
 
If you enter this code, you will see a query statement returned:

Select contactname from MERs
Where customerid =''
Union all select contactname from MERs
Where customerid <>''

By obtaining the Union of empty and non-empty customer IDs, this query statement returns all relevant names in the database. In fact, this Union technique can be used to obtain a large majority of information in your database. Let's look at the value of customerid:

'Union all select firstname + ''+ lastname from
Employees where lastname <>'

It converts the SQL statement:

Select contactname from MERs
Where customerid =''
Union all select firstname + ''+ lastname from
Employees where lastname <>''
 
Look, it's the name of the first employee the attack program obtained from your database.

More attack programs
If SQL injection only has the weakness of data exposure, it will be bad enough. However, in fact, a good attack program can use this weakness to obtain all the information in your database. Let's look at the example below:

'; Drop table MERs MERS ;--

The SQL statement is changed:

Select contactname from MERs
Where customerid =''
; Drop table MERs MERS ;--'

This semicolon isolates the statement from SQL Server. Therefore, there are actually two statements. The name of the first statement does not exist, and the second statement revokes the entire MERs table. Two-SQL Server annotator, which can enable the clause to avoid syntax errors.

With this technology variation, an attack program can run on any SQL statement or stored procedure. By using xp_mongoshell to expand the stored procedure, an attack program can also run under the operating system command. Obviously, this is a serious vulnerability.

Protect your own database
Now, do you know how to prevent SQL injection attacks? First, you cannot construct a where clause in user input. You should use parameters to use stored processes. On the initial ASP page, the rewritten part is similar to what we saw in the table. Even if you think there are no vulnerabilities in your application, you should follow the principle of least privilege. Use other security technologies we recommend to allow your users to access only what they can. When you do not find your database vulnerabilities, this will not cause your database to crash.

Final suggestions
This is all SQL Server Security Series. Maybe you are not a comprehensive expert now, but you have learned a lot about the opposite. The next step is to protect your SQL server data, remember what you learned here, and use your database to prevent your data from being attacked by those hackers.