Search code examples
sqlvb.netcode-injection

SQL Parameters within long String with VBNET

i am trying to make my server injection proof, and have a log table where I store human readable events. Is it possible to put a sql parameter within the Logtext string? In order to prevent any malicious input to come via the maliciousString?

Dim User as String = "busssard"
Dim Logtext As String = "User performed modification " & maliciousString
myCommand = sqlconnection.CreateCommand()
myCommand.CommandText = "INSERT INTO dbo.Logging ([FK_User],[Change]) Values(@Username, '" & Logtext & "')"
myCommand.Parameters.AddWithValue("@Username",User)

There are workarounds, but i would like not to change too much in the whole DB structure..

Solution

  • Don't use AddWithValue at all. Use Add and ALWAYS specify the size for columns that have one. Do it for both your values. Let's say that FK_User is varchar(50) and Change is nvarchar(max):

    myCommand.CommandText = "INSERT INTO dbo.Logging ([FK_User], [Change]) VALUES (@FK_User, @Change)"
myCommand.Parameters.Add("@FK_User", SqlDbType.VarChar, 50).Value = user
myCommand.Parameters.Add("@Change", SqlDbType.NVarChar, -1).Value = logText