Q: How to send long SQL Statements via ODBC? And: sql-Injection possible in ASP?

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.

>From a vertain Stament length on, Statements are not executed on the
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
....

Re: How to send long SQL Statements via ODBC? And: sql-Injection possible in ASP?

marco wrote:
> 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.

Why not use a stored procedure? That would certainly help reduce network
traffic ...
>
>> From a vertain Stament length on, Statements are not executed on the
> 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.

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.
>
> A) How to design a long SQL-statement with respect to the supposed
> commandlength restriction in the ODBC-Driver correctly?

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

> 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.

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

> Who knows how to?
>
> B) Is it in classic ASP like in .NET possible to use SQL-injection,

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

> e.g. to create an IdbCommand and set its parameters before execution?
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.inetser ver.asp.db/msg/72e36562fee7804e

> 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?

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


>
> 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
> ...

--
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"

Re: How to send long SQL Statements via ODBC? And: sql-Injection possible in ASP?

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