Webmaster Forum / ASP / Database Access / May 2007

Hi,

in an classic ASP with SQL Server 2000 I'm old-stylish generating SQL
Statemnents in vbs code and send them then via conn.execute. The
statements are packend in a transaction.

Server. If the same Statement is copied into the QueryAnalyzer, the
Statement is executed correctly. I suppose, the ODBC-driver supports
only a maximum length of e.g. 2000 characters for a command and
truncates the rest. Because all statements are part of a transaction,
nothing is executed.

A) How to design a long SQL-statement with respect to the supposed
commandlength restriction in the ODBC-Driver correctly? The
transaction is needed. I don't want to fire every single statement.
The number of contained SQL-statements and their parameters differ so
much, that I can not imagine a stored procedure that does this work.
Who knows how to?

B) Is it in classic ASP like in  .NET possible to use SQL-injection,
e.g. to create an IdbCommand and set its parameters before execution?
Till now I build one long String including the 'Parameter'-Values. But
it's hard to handle all signs like " and ' . How to do this better
please?

Thank you in advance!

Regards
Marco

A)

This is my coding so fare:

set Conn = CreateObject("ADODB.Connection")
Conn.open "DSN=...;UID=...;PWD=..."

sql = "Begin Tran T1 "
sql = sql & "INSERT INTO tblBeispiel... ;"
sql = sql & "DELETE FROM tblBeispiel2 ... WHERE ...;"
sql = sql & "DELETE FROM tblBeispiel2 ... WHERE ...;"
.....
sql = sql & "UPDATE tblBeispiel3 SET ... WHERE ;"
sql = sql & "COMMIT Tran T1"

Conn.execute sql
Conn.Close

B)

Does something like this work in classic ASP? :

Dim conn As IDbConnection = ...

Dim query As String = "SELECT * FROM tblUsers WHERE uname = @mypara"

Dim cmd As IDbCommand = conn.CreateCommand
cmd.CommandText = query

Dim param As IDataParameter = cmd.CreateParameter
param.ParameterName = "@mypara"
param.Value = "Meier"
param.DbType = DbType.String
cmd.Parameters.Add(param)

Dim rdr As IDataReader = cmd.ExecuteReader
...