inetserver asp db: 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.

[quoted text, click to view]
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
....

[quoted text, click to view]

Why not use a stored procedure? That would certainly help reduce network
traffic ...
[quoted text, click to view]

I've never seen anything like that, but, of course, I would never try to
pass a batch of that size to the database. A batch that size is certainly a
candidate for a stored procedure.
[quoted text, click to view]

Don't. Create a stored procedure and use SQL Server's transaction-handler.

[quoted text, click to view]

Why not? I can't imagine a stored procedure not being able to accomplish
this task.

[quoted text, click to view]

Umm, you have the wrong idea about what SQL Injection is. SQL Injection is
something you need to prevent by using parameters. Read these:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

[quoted text, click to view]
Of course. However, in vbscript, unless you are executing a stored procedure
that returns information via output or return parameters, it's a waste of
time. Pass the parameters via a variant array. See:

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

[quoted text, click to view]

See above. Also, see this for the various ways to execute a stored
procedure:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&


[quoted text, click to view]

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob, thank you.
I trhink you are right: This can't be problem of the length of the sql
command.
So I'll check out, if I can do more with SPs.
And I found the ADODB Command Object which allows to append
parameters.
Together with sql Servers Transaction this will help me.
Yes, I misused the 'sql-injections' .
Thank you again.

Regards
Marco