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
– GSerg, Commented Jul 5, 2019 at 9:33
On top of that, if this is vbscript, you might want to look at stackoverflow.com/a/39278153/11683. — GSerg
– GSerg, Commented Jul 5, 2019 at 9:41
This should help with structuring a parameterised INSERT statement using ADODB.Command. — user692942
– user692942, Commented Jul 5, 2019 at 11:18

Mr Kaos · Accepted Answer · 2022-02-19 11:31:47Z

0

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.

answered Feb 19, 2022 at 11:31

Mr Kaos

134 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL parameterized insert query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related