SQL Server - Transact SQL / SQL with Parameters Newbie Question

Asked By Toni

18-Nov-09 05:09 PM

I run a website written in ASP (VBScript). I have 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 do not know if I enter this in my ASP code, or if these
are stored procedure examples.

I am really a complete newbie to using parameters and I confess that a Google search for
parameters! The Microsoft reference is no help
(http://msdn.microsoft.com/en-us/library/ms189826%28SQL.90%29.aspx).

HELP!

Toni

18-Nov-09 06:51 PM

Toni (Toni24@yahoo.com) writes:

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 is commonly known as).


I cannot give an example of using parameterised commands in ASP, because
I have 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 " & _
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, esquel@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

18-Nov-09 10:15 PM

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

20-Nov-09 12:22 AM

Toni (Toni24@yahoo.com) writes:

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.

--
Erland Sommarskog, SQL Server MVP, esquel@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