A couple of nights ago, I was working on some ASP pages using JScript (I know, old technology by .Net's standards) for one of my clients. Because of a requirement, I was writing ADO code using JScript to call stored procedures I had created in SQL Server 2000. I wanted to use the advice I gave previously to use the ADO Command object when setting parameters for the stored procedures. What I didn't know was how problematic JScript is with the Command.Execute method. I wanted to use this construct: (similar to what you can do in VB):
cmd.Execute(recordsAffected, , adExecuteNoRecords);
but, because JScript won't accept “optional” parameters in the middle of a call (which is indicated by the blank field), I kept getting an error.
Finally, by simply running this:
cmd.Execute();
I got it working. I could have also put in the recordsAffected by declaring
var recordsAffected = ““; // In order to force JScript to use this as a reference value. Otherwise, it will use it as a number object, which JScript always sets to a regular value and would return 0.
When trying to figure this out, I was amazed at the approach of others in a Google search. So many gave up on the whole ADO Command approach and opted for the “concatenate the parameters and be done with it” approach. Never, ever, ever do this!! This is exactly how SQL Injection can happen. This applies equally to .Net. With .Net, always use the SQLParameter collection to build your parameters.
On a side note regarding SQL Security, if you haven't already applied SQL Server 2000 SP3a (this came out late September), do so now! One nice addition to this last service pack is the update of SQL Server 2000 Books Online (to SP3). In particular, there are more security articles listed. You can find the update separately here. I am assuming the above Service Pack installs the updated books online, because I had already updated the books online after SP3 and before SP3a.
One SQL Security article I found and really liked is “Validating User Input”. This describes many of the best practices for SQL Security, including all the characters you should reject for possible SQL Injection:
| Input character | Meaning in Transact-SQL |
|---|---|
| ; | Query delimiter |
| ' | Character data string delimiter |
| -- | Comment delimiter |
| /* ... */ | Comment delimiters. Text between /* and */ is not evaluated by the server. |
| xp_ | Begins the name of catalog extended stored procedures such as xp_cmdshell. |
Some of these apply to Oracle, and other databases, as well. Great stuff.
