Menu
I'm about to have to deal with some SQL code in classic ASP VBScript.
I have two questions.
First, in .net, I'm used to using the System.Data.SqlClient namespace objects to perform queries. For example:
Dim conn as New SqlConnection("Data Source=MyServer;uid=myUid;pwd=myPwd;Initial Catalog=myDataBase;"
Dim cmd as New SqlCommand("Select fname From myTable where uid=@uid;", conn)
cmd.Parameters.add(New SqlParameter("@uid",100323)
conn.open()
Response.Write(cmd.ExecuteScalar())
conn.Close()
I've been told that using a parameterized query as such makes my query secure from SQL injection attacks.
I'd like to know what is the equivalent code to do such a query in classic ASP with VBScript and what similar security precautions must be used to guard against SQL injection.
516
Parameterized queries are queries that have one or more embedded parameters in the SQL statement. This method of embedding parameters into a SQL statement is less prone to errors than the method of dynamically building up a SQL string. Now, on with the code/lesson...
Further, since the parts of the SQL statement are added as parameters, the same code can be reused. Now let us create a parameterized SQL statement in ASP.NET. As usual, we would be required to first create our connection and command objects. We would then add parameters to it before it is executed.
Parameters can prompt the user for an input value when the query is run or refreshed, use a constant as the input value, or use the contents of a specified cell as the input value. A parameter is part of the query it modifies, and cannot be reused in other queries.
The parameter values are added by simple text replacement before the query is sent to the server, so they can be used anywhere. Table names, database names, server names, string literals, or pieces of any of those. The script can be multiple batches separated by GO, and the SQLCMD variables are applied to the whole thing.
There are ADODB Objects which do basically the same thing. ADODB.Command object is the equivalent to SqlCommand. From there it is basically doing the same as in .NET.
set cmd = Server.CreateOject("ADODB.Command")
cmd.CommandText = "select From Table where ID = @id")
set param = cmd.CreateParameter("@id", adInteger, adInput,0,0)
I frequently use w3schools for help about ADO objects.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
