Improper preparation of SQL statements leads to system insecurity

In a general multi-user application system, only users with the correct user name and password can access the system. We usually need to write a user logon window to control the user's use of the system. Here we use Visual Basic + ADO as an example:

I. Vulnerability generation

Logon table

Users (name, pwd)

Create a form Frmlogin with two text boxes Text1, Text2, and cmdok and cmdexit. The two text boxes are used for the user to enter the user name and password respectively, and the two command buttons are used for "login" and "exit ".

1. Define the Ado Connection object and ADO RecordSet object:

Option Explicit
Dim Adocon As ADODB. Connection
Dim Adors As ADODB. Recordset
 

2. perform database connection in Form_Load:

Set Adocon = New ADODB. Connection
Adocon. CursorLocation = adUseClient
Adocon. Open "Provider = Microsoft. jet. OLeDB.4.0.1; Data Source = "&&_
App. Path & "est. mdb ;"
Code in cmdok
Dim sqlstr As String
Sqlstr = "select * from usersswheresname =" & Text1.Text &&_
"And pwd =" & Text2.Text &&""
Set adors = New ADODB. Recordset
Set Adors = Adocon. Execute (sqlstr)
If Adors. Recordcount> 0 Then // or If Not Adors. EOF then
....
MsgBox "Pass" // verification passed
Else
...
MsgBox "Fail" // Verification Failed
End if
 

Run this program. It seems that there is no problem in doing so, but when you enter any string (such as 123) in Text1 and a or a = a in Text2, let's look at the value of sqlstr at this time:

Select * from usersswheresname = 123 and pwd = a or a =
 

Execute such an SQL statement. Because the = a after or is true, as long as there is a record in the users table, the returned eof value must be False, this easily bypasses the system's authentication of users and passwords.

This problem will occur in all the systems that use select * from usersswheresname = "& name &" and pwd = "& password, whether you are using that programming language.

Ii. Features of Vulnerabilities

On the Internet, the above problems are particularly obvious. I have found on many websites that this method can be used to access the system that requires user name and password verification. Such an SQL vulnerability has the following features:

1. It has nothing to do with programming languages or technologies

Whether it is using VB, Delphi, ASP, JSP.

2. Concealment

A considerable number of existing systems have this vulnerability, which is hard to detect.

3. Hazard

You can easily access the system without guessing the user name or password.

Iii. Solutions to vulnerabilities

1. The password cannot contain spaces.

2. encrypt the password.

Here, we should mention that encryption cannot adopt an overly simple algorithm, because an overly simple algorithm will allow people to construct ciphertext, such as a or a = a, to enter the system.

3. Separate user authentication and password verification. Perform User Authentication first. If the user exists, perform password verification again, which can solve the problem.