Some programming situations require you to use Dynamic SQL. Of course the problem with using Dynamic SQL is that this can lead to SQL Injection attacks. However, you can avoid these problems, by just changing how you submit Dynamic SQL to your back end database.
To illustrate this, consider the sample table of users listed below.
CREATE TABLE User
(
Login char(16) not null primary key,
Password varchar(20) not null
)
go
INSERT INTO User values('PSheriff', 'password')
go
INSERT INTO User values('Keng', 'password')
go
You can copy and paste the above SQL code into your database management system and create this table. After you have created this table, you might create a login screen and use some code like the following to see if the users are in the User table.
Private Sub BADLoginCode()
Dim sql As String
Dim cmd As SqlClient.SqlCommand
Dim rows As Integer
sql = " SELECT Count(*) As TotalRows FROM User "
sql &= " WHERE Login = '" & txtLogin.Text & "'"
sql &= " AND Password = '" & txtPassword.Text & "'"
cmd = New SqlClient.SqlCommand(sql)
cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)
cmd.Connection.Open()
rows = Convert.ToInt32(cmd.ExecuteScalar())
If rows > 0 Then
MessageBox.Show("Success")
Else
MessageBox.Show("Failure")
End If
End Sub
The problem with the above code is if a hacker where to type in the following into the Login ID field;
' OR 1=1 DELETE FROM Users --
Then the resulting SQL that is submitted to the back end database is the following:
SELECT Count(*) As TotalRows
FROM User
WHERE Login = '' OR 1=1
DELETE FROM User --'
AND Password = ''
As you can see this will not only allow the user into the application, but will also delete all other users in the system! This is not a good thing.
However, with just a very minor change to this code, you can protect yourself against this type of attack. Just like when calling a stored procedure you use Parameters on the command object to submit the login id and password to the input parameters, you can do the same type of coding with dynamic SQL.
Private Sub LoginGood()
Dim sql As String
Dim cmd As SqlClient.SqlCommand
Dim rows As Integer
sql = " SELECT Count(*) As TotalRows FROM User "
sql &= " WHERE Login = @sLogin "
sql &= " AND Password = @sPassword"
cmd = New SqlClient.SqlCommand(sql)
cmd.Connection = New SqlClient.SqlConnection(AppConfig.ConnectString)
cmd.Connection.Open()
cmd.Parameters.Add(New _
SqlClient.SqlParameter("@Login", SqlDbType.Char))
cmd.Parameters.Add(New _
SqlClient.SqlParameter("@Password", SqlDbType.Char))
cmd.Parameters.Item("@Login").Value = txtLogin.Text
cmd.Parameters.Item("@Password").Value = txtPassword.Text
rows = Convert.ToInt32(cmd.ExecuteScalar())
If rows > 0 Then
MessageBox.Show("Success")
Else
MessageBox.Show("Failure")
End If
End Sub
Notice the use of the Parameters in the dynamic SQL. When you now run this code, the command object and the parameters ensure that no SQL injection attacks will affect your code.