Parameters On SQL Command 2

[vtops]

Dear All,

I have this code :

Dim db1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim param1, param2 As New ADODB.Parameter

db1.ConnectionString = "DSN=test database"
db1.Open
cmd1.ActiveConnection = db1


Set param2 = cmd1.CreateParameter("param1", adLongVarChar, adParamInput, 30)
cmd1.Parameters.Append param1
cmd1.Parameters("param1").Value = "test1"

Set param1 = cmd1.CreateParameter("param2", adLongVarChar, adParamInput, 30)
cmd1.Parameters.Append param2
cmd1.Parameters("param2").Value = "test2"


cmd1.CommandText = "insert into table1 (code,descr) values(?,?)"
cmd1.Execute
db1.Close

If I write the sql statement like this i it will take the param1 value in the field code and the param2 value in the field descr.

If i declare first the param2 and after the param1 it will do the opposite.

So i am wondering if i can put in the sql statement the parameter's names like :

cmd1.CommandText = "insert into table1 (code,descr) values(param1,param2)" etc.


Thanks a lot
Best Regards

 

[TysonLPrice]

Just spit balling here but could you...

Dim strParam1 as string
Dim strParam2 as string
strParam1 = "test1"
strParam2 = "test2"
cmd1.CommandText = "insert into table1 (code,descr) values(" & strparam1 & "," & strparam2 & ")

 

[Sheco]

You could skip the command object altogether:

Dim db1 As New ADODB.Connection
db1.ConnectionString = "DSN=test database"
db1.Open
db1.Execute "insert into table1 (code,descr) values('test1','test2')"
db1.Close
set db1 = nothing

 

[bjd4jc]

>>You could skip the command object altogether

I would not reccommend doing this for a production application.

>>So i am wondering if i can put in the sql statement the parameter's names like

have you tried it? sometimes you can figure things out simply by trial and error. This would be the perfect example of doing such a thing.

 

[Sheco]

I figure if it was a production application we'd be talking about a stored procedure.

 

[BobRodes]

Yeah, the code in your first post is verklempt. You're confusing stored procs with sql strings. As several respondents have said, you don't have to use a command object when you're using sql strings, although you can. So:

    Dim db1 As New ADODB.Connection
    Dim cmd1 As New ADODB.Command
    Dim param1, param2 As New ADODB.Parameter
    [COLOR=green]'the above is not best practice, by the way[/color]
    db1.ConnectionString = "DSN=test database"
    db1.Open
 [COLOR=red]cmd1.CommandText = "insert into table1 (code,descr) values 'test1', 'test2'"[/color]
cmd1.Execute
db1.Close

Should work fine, without any parameter objects at all. (I notice, by the way, that your param1 and param2 are backwards.) If you wanted to get melodramatic:

    Dim db1 As New ADODB.Connection
    Dim cmd1 As New ADODB.Command
    Dim param1, param2 As New ADODB.Parameter
    
    db1.ConnectionString = "DSN=test database"
    db1.Open
    cmd1.ActiveConnection = db1
   
   
   Set param1 = cmd1.CreateParameter("param1",    adLongVarChar, adParamInput, 30)
   cmd1.Parameters.Append param1
   cmd1.Parameters("param1").Value = "test1"
       
    Set param2 = cmd1.CreateParameter("param2", adLongVarChar, adParamInput, 30)
    cmd1.Parameters.Append param2
    cmd1.Parameters("param2").Value = "test2"
    
     
 [COLOR=red]cmd1.CommandText = "insert into table1 (code,descr) values '" & cmd1.parameters(0).value & "', '" & cmd1.parameters(1).value & "'"[/color]
cmd1.Execute
db1.Close

Which of course is horribly roundabout.

So, the reason to use parameter objects is when stored procs require parameters. Let's assume that you have one called sp_instbl1. Then:

    Dim db1 As ADODB.Connection
    Dim cmd1 As ADODB.Command
    Dim param1, param2 As ADODB.Parameter

    Set db1 As New ADODB.Connection
    Set cmd1 As New ADODB.Command
    [COLOR=green]'Best practice.  Also, it's unnecessary to Set the param objects here.  
    'You do that with the CreateParameter method.[/color]
    
    db1.ConnectionString = "DSN=test database"
    db1.Open

    with cmd1
        .ActiveConnection = db1
        Set param1 = .CreateParameter("param1",    adLongVarChar, adParamInput, 30)
        .Parameters.Append param1
        .Parameters("param1").Value = "test1"
        Set param2 = .CreateParameter("param2", adLongVarChar, adParamInput, 30)
        .Parameters.Append param2
        .Parameters("param2").Value = "test2"
        [COLOR=red].CommandType = adCmdStoredProc
        .CommandText = "sp_instbl1"[/color]
        .Execute
    End With
    db1.Close

This will execute the stored proc, passing the parameters that you have defined.

Finally, I would strongly recommend you read chiph's faq on sql injection attacks.

HTH

Bob

 

[Sheco]

[tt]Dim param1, param2 As ADODB.Parameter[/tt]

Will param1 be a variant?

 

[bjd4jc]

>>the reason to use parameter objects is when stored procs require parameters

I would agree that it is a reason but not the reason. I would say the use of parameters to fend off any SQL Injection attacks would also be a good reason for using the command object with parameters.

 

[BobRodes]

Missed that, Sheco, quite right.

bjd4jc, how would you use parameters independently of stored procs to fend off injection attacks? I don't get it.

Bob

 

[bjd4jc]

Bob-

Sorry I have taken so long to get back here to this. I have been tied up.

Consider these two example blocks of code

    strValue = "Bob' OR 'x' = 'x"

    Set cmd = New ADODB.Command
    cmd.CommandType = adCmdText
    cmd.ActiveConnection = con
    cmd.CommandText = "SELECT * FROM Emps WHERE FirstName = ?"
    cmd.Parameters.Append cmd.CreateParameter("@Value1", adVarChar, , 50, strValue)
    Set rs = cmd.Execute
    Debug.Print rs.EOF

    Set rs = con.Execute("SELECT * FROM Emps WHERE FirstName = '" & strValue & "'")
    Debug.Print rs.EOF

This is just a sample of what can happen and how you can use parameters to solve the issue. The results that you get are: no records for the first query unless there is someone with a first name of exactly "Bob' OR 'x' = 'x", which if there were, standard security would then apply. The last query gives all of the records in the table.

My point is, you don't have to use stored procedure to avert injection attacks.

 

[chiph]

you don't have to use a command object when you're using sql strings, although you can.

I would change this to say: you don't have to use a command object when you're using sql strings, although you probably should.

This is because SQL Parameter objects protect you from SQL injection attacks (they filter out the bad character sequences that allow attacks to succeed).

A side benefit is that you get 80-90% of the speed boost of a stored procedure, but with more maintainable code. This is because the parameterized query gets stored in the database's procedure cache after it gets parsed & validated the first time (just like stored procedures), so the second time you run the query all that work has already been done, and your query runs much faster.

The only caveat to this is that the query must be absolutely identical, down to the connection string. So if you're running integrated security, each user will get their own cache, and the contents won't be shared. But if you have a common userid & password in your connection string, then all users share the same cache contents, and the performance boost is much more dramatic.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[BobRodes]

This is very useful information, bjd and Chip. Star for both of you, and thanks for sharing.

Bob