Visual Basic - add parameters to adodb.recordset object

Author

add parameters to adodb.recordset object

Eitan

2004-10-29, 8:55 am

Hello,
I am using ADO, in VB 6.0

How can I add parameters to sql query (by something like parameter object) ?

I am doing the follows :
dim sql as string
dim rs as new adodb.recordset

sql = "select " & col_1 & " from my_table")
call rs.execute(sql)

Can I do as above in some other way (using parameter) ?

Thanks :)

Al Reid

2004-10-29, 8:55 am

Mark J. McGinty

2004-10-29, 3:55 pm

"Eitan" <nobody@nospam_please.com> wrote in message
news:%23qkXw9YvEHA.3152@TK2MSFTNGP14.phx.gbl...
> Hello,
> I am using ADO, in VB 6.0
>
> How can I add parameters to sql query (by something like parameter object)
> ?
>
> I am doing the follows :
> dim sql as string
> dim rs as new adodb.recordset
>
> sql = "select " & col_1 & " from my_table")
> call rs.execute(sql)
>
>
> Can I do as above in some other way (using parameter) ?

A column name cannot be specified as a parameter, nor can a table name nor
any SQL key words. A parameter can be used to pass a source column value in
an UPDATE or INSERT statement, or as criteria on either side of the
comparison operator in a WHERE clause.

The only way to splice-in columns on the fly is to dynamically generate the
SQL and execute it, as you've done, but it must be noted that incorporating
user input into a SQL statement is a very dangerous business. Consider you
example, and suppose the value of col_1 is user-entered. Now imagine a
maliscous user, who has inferred the construction of your SQL and enters "0;
DELETE" (without the quotes, of course.) If your code executed the
resulting SQL, all rows in my_table would be lost to a SQL Injection attack.

Or how about this instead: "TABLE_NAME FROM INFORMATION_SCHEMA.TABLES -- "
The -- at the end negates the rest of your intended SQL and preserves
syntax. Now the ostensible attacker has not only a way to pass SQL of his
own construction to your server, he also has carnal knowledge of your
schema! At this point, to use the vernacular, he "ownz" your server.

Point of all this being that if you're going to use dynamic SQL, you must
design your code very carefully, and give special consideration to the
possibility of bad input purposely mal-constructed by unsavory users...
because they are out there, and they are looking, and if they find you have
written weak/vulnerable code, they'll have a field day at your expense.

-Mark

> Thanks :)
>
>

Val Mazur

2004-10-30, 3:55 am

Hi,

It depends on what you need to do. If you need to pass list of the fields as
a parameters, then you cannot do this as a parameter. You can only pass a
value for the condition as a parameter. But it has some limitations (minor)
as well. For example, you cannot pass value for IN clause.
In your case, when you need to pass unspecified field(s) name, then you
would need to concatenate SQL connection string, but if your field name
comes as an external input, you would need to make a good validation what
is passed on, to be sure that you do not face SQL injection issue.

--
Val Mazur
Microsoft MVP

"Eitan" <nobody@nospam_please.com> wrote in message
news:%23qkXw9YvEHA.3152@TK2MSFTNGP14.phx.gbl...
> Hello,
> I am using ADO, in VB 6.0
>
> How can I add parameters to sql query (by something like parameter object)
> ?
>
> I am doing the follows :
> dim sql as string
> dim rs as new adodb.recordset
>
> sql = "select " & col_1 & " from my_table")
> call rs.execute(sql)
>
>
> Can I do as above in some other way (using parameter) ?
>
> Thanks :)
>
>

Sponsored Links