Microsoft-SQL-Server

Classic ASP might seem like a language that is dead and gone, but it is still alive…somehow. With a language that has become outdated, it can be difficult to fight against modern security risks. Knowing how to prevent SQL injection with classic ASP is a valuable bit of code to have at your disposal. With bots capable of hacking sites, you don’t to make things easy for them. Thankfully, there is a way to setup prepared statements using Classic ASP.

If you are familiar with prepared statements, this shouldn’t be too much trouble. I will admint, this is a pretty ugly implementation, but ASP isn’t exactly bleeding edge, so this is the best we have. The first and most awkward thing about prepared statements with classic ASP, is that you need to declare the data type. For example, if a field in a DB is of type int, you need to declare this when creating the statement. It seems odd, but this is how it goes. the following code will show you a quick and easy way to pull a row from a database by using an ID that is passed in the querystring.

The only thing that needs to change are the parameters that you pass into the CreateParameter function. As I mentioned previously, you need to declare the data type when adding a command parameter. A full list of all of the data type codes can be found here http://www.w3schools.com/asp/met_comm_createparameter.asp

This is a pretty solid way to prevent sql injection with classic ASP. Nothing is ever bulletproof, so always be on the lookout for ways to further improve the security by validating data even further to prevent any bad data making its way into a query string.

 

Microsoft-SQL-Server

When using Sql Server to insert records into a DB its a common requirement to be able to pull back the auto incremented Id that was generated for this row. Most solutions for this i have seen involve making a second query to the database in and along the lines of  “SELECT @@IDENTITY”, but this method seems quite wasteful. It seems like a simple request that the database would return the value automatically without you having to ask for it with a second wasteful query. Well, it turns out that it’s incredibly simple to do the auto increment ID from SQL Server after an insert is performed.

The auto incremented value can be easily returned by using the OUTPUT Inserted.<auto id col name>. To put this in a real life example, the following query will insert a user into the Users database and will return the auto ID. In this case there is a column called “id” and this is what i am asking the DB to return in the query.

Using this query from whatever language you use will make the DB return the auto incremented ID from an SQL Server database. Here is an example of how you would use the above query in C# to get the auto incremented ID back from the database.