SQL Parameters within long String with VBNET-CodePudding

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..

CodePudding user response：

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