Transact SQL / SQL with Parameters Newbie Question

[Toni]

I run a website written in ASP (VBScript). I've been trying to teach myself using SQL
with parameters so I can head off some SQL injection attacks (that so far I have been
successful in stopping). I want to learn one step at a time, and so be able to access my
MS SQL 2005 database using parameters *before* I try creating a stored procedure.

Is "Transact SQL" the same thing as "SQL with parameters"?

Also, WHERE do I use/write Transact SQL? I see lots of code examples online, starting
with the DECLARE statement, but I don't know if I enter this in my ASP code, or if these
are stored procedure examples.

I'm really a complete newbie to using parameters and I confess that a Google search for
"Transact SQL" results in websites that seem to assume I already know how to use
parameters! The Microsoft reference is no help
(http://msdn.microsoft.com/en-us/library/ms189826%28SQL.90%29.aspx).

HELP!

Toni

[Erland Sommarskog]

Toni (Ton...@yahoo.com) writes:
> I run a website written in ASP (VBScript). I've been trying to teach
> myself using SQL with parameters so I can head off some SQL injection
> attacks (that so far I have been successful in stopping). I want to
> learn one step at a time, and so be able to access my MS SQL 2005
> database using parameters *before* I try creating a stored procedure.
>
> Is "Transact SQL" the same thing as "SQL with parameters"?
>
> Also, WHERE do I use/write Transact SQL? I see lots of code examples
> online, starting with the DECLARE statement, but I don't know if I enter
> this in my ASP code, or if these are stored procedure examples.

Transact-SQL is the SQL dialect that SQL Server users. No matter you
inline parameter values, or you parameterise your commands, you use
Transact-SQL (or T-SQL as it's commonly known as).

> I'm really a complete newbie to using parameters and I confess that a
> Google search for "Transact SQL" results in websites that seem to assume
> I already know how to use parameters! The Microsoft reference is no help
> (http://msdn.microsoft.com/en-us/library/ms189826%28SQL.90%29.aspx).

I can't give an example of using parameterised commands in ASP, because
I've never worked with ASP. (By the way, ASP is a fairly old technology.
You should probably look at ASP .Net instead.)

But here is an example of using parameterised commands with ADO in
Visual Basic, which should be similar enough to VBscript:

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@custid", adWChar, adParamInput, 5, custid)
End If

If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@shipname", _
adVarWChar, adParamInput, 40, shipname)
End If

Set rs = cmd.Execute

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Toni]

"Erland Sommarskog" wrote...
:

> But here is an example of using parameterised commands with ADO in
> Visual Basic, which should be similar enough to VBscript:
>
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = cnn
>
> cmd.CommandType = adCmdText
> cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
> " FROM dbo.Orders WHERE 1 = 1 "
> If custid <> "" Then
> cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
> cmd.Parameters.Append
> cmd.CreateParameter("@custid", adWChar, adParamInput, 5, custid)
> End If
>
> If shipname <> "" Then
> cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
> cmd.Parameters.Append cmd.CreateParameter("@shipname", _
> adVarWChar, adParamInput, 40, shipname)
> End If
>
> Set rs = cmd.Execute

Erland, thanks, but can you point me to any documentation that would help me understand
what you've written above, please???

[Erland Sommarskog]

The ? are parameter markers and are specific to the OLE DB and ODBC
API:s; they are not proper T-SQL. OLE DB and ODBC will transslate this
into proper T-SQL.

The Parameters collection is descibed here:
http://msdn.microsoft.com/en-us/library/ms675869%28VS.85%29.aspx

But the reference manual for ADO is not really written for people with
hardly no background knowledge, so it is not a good place to get started.
You need to click around a lot on the various links, to go CreateParameter
etc.

The ADO Programmer's Guide may be a better start, but I have not read it
myself. http://msdn.microsoft.com/en-us/library/ms681025%28VS.85%29.aspx.

Or you could get a book on ADO. David Datta wrote one many year's ago
that I liked: "Programming ADO". It may be hard to find, though. As I
said, ADO is yesterday's technology.