SQL parameterized insert query

Question

I'm trying to use the parameterizartion to prevent SQL injection in one of the textbox in the HMI I working with.

Have looked a lot for a solution what I gathered is depending on what SQL syntax there is I can use either ? or @ to tell the system that is parameter but both of them are throwing an error.

Dim DBCommand
Dim DBRecordSet 
Dim Connection
Dim sqlString   

Set DBRecordSet = CreateObject("ADODB.Recordset") 
Set DBCommand = CreateObject("ADODB.Command")
Set Connection = GetDBConnection("Test")

sqlString = "Insert into [WorkCommentLog] (Worklog_WorkID, Comment, 
InsertTime, WrittenBy) values" &_
"('"& WorkID &"' , @Comm , GetDate() , '" & User.Value &"');"

DBCommand.Parameters.Append DBCommand.CreateParameter ("@Comm", 
adVarChar, adParamInput, 255, WinCC_Comment.Value)

DBCommand.CommandText = sqlString
DBCommand.Execute(adExecuteNoRecords)

Connection.Close

Dim DBCommand
Dim DBRecordSet 
Dim Connection
Dim sqlString   

Set DBRecordSet = CreateObject("ADODB.Recordset") 
Set DBCommand = CreateObject("ADODB.Command")
Set Connection = GetDBConnection("Test")

sqlString = "Insert into [WorkCommentLog] (Worklog_WorkID, Comment, InsertTime, WrittenBy) values" &_
"('"& WorkID &"' , ? , GetDate() , '" & User.Value &"');"

DBCommand.Parameters.Append DBCommand.CreateParameter ("Comment", adVarChar, adParamInput, 255, WinCC_Comment.Value)
DBCommand.CommandText = sqlString
DBCommand.Execute(adExecuteNoRecords)

Connection.Close

The first code snippet throws this error:

Must declare the scalar variable "@Com"

while the second code snippet throws this error:

No Value given for one or more required parameters

`I can use either ? or @` - it's not your choice, it depends on what database provider you are using. What does `GetDBConnection` do? On a side note, parametrizing only some of the parameters is like parameterizing none when it comes to security. — GSerg, Jul 05 '19 at 09:33
On top of that, if this is vbscript, you might want to look at https://stackoverflow.com/a/39278153/11683. — GSerg, Jul 05 '19 at 09:41
[This should help](https://stackoverflow.com/a/22729750/692942) with structuring a parameterised `INSERT` statement using `ADODB.Command`. — user692942, Jul 05 '19 at 11:18

score 0 · Answer 1 · answered Feb 19 '22 at 11:31

I have found that executing a parametrized query using VBScript like so has always worked for me:

Set command = CreateObject("ADODB.Command")
Set command.ActiveConnection = Connection

command.CommandText = "sp0001_ExampleStoredProcedure" ' The name of the stored procedure in my database that has the parametrized query.
command.CommandType = 4
command.Parameters("@Parameter1") = "parameterValue"
command.Parameters("@Parameter2") = "parameterValue"
command.Execute

Note that this example is executing a parametrized stored procedure in my database. You could try writing your insert query into a stored procedure in your database instead of hard-coding your query in the VBScript file.

SQL parameterized insert query

1 Answers1