|
|
NET Refactor - SQL ParametersCleaing up Dynamic SQLUsing Dynamic SQL is a bad habit that many developers must admit to having used. And, even worse, when you start to use it, you don't just use it once or twice. You use it so many times over a project that you may be ashamed to admit it. Old habits die hard!. Dynamic SQL is dangerous. On the Web, it opens your site to injections attacks and hacking. In a desktop or Client Server application, it opens the door to the old single quote (name = O'Rielly) bug-a-boo, to which all who would be honest, have been a victom. The SQL Parameters menu option of NET Refactor, found under the SQL Refactor Menu, converts dynamic parameters to the use of Database Command.Parameters automatically. To use thisis option, select the whole block of code which contains SQL with dynamic parameters, and click the SQL Parameters menu option. If you are still wondering what Dynamic SQL is, an example is shown below.
sql = "Select * "
sql &= "from table "
sql &= "where a = '" & item & "' "
sql &= "and d = '" & DateAdd(DateInterval.Day, 1, Today) & "' "
sql &= "and b = " & asf & " and e = '4' "
sql &= "and date2 = '" & Format(Today, "MM/dd/yyyy") & "' "
sql &= "and date1 = '" & DateAdd(DateInterval.Day, 1, Today) & "' "
sql &= " order by b"
|
Once the menu option is selected the code will be extracted from the code window and placed into the dialog shown below.
The code shown below shows the result of the conversion. Notice that all single quotes are gone; so are the problems associated with them.
sql = "Select * "
sql &= "from table "
sql &= "where a = @dbParam1 "
sql &= "and d = @dbParam3 "
sql &= "and b = @dbParam2 and e = '4' "
sql &= "and date2 = @dbParam4 "
sql &= "and date1 = @dbParam5 "
sql &= " order by b"
Dim sqlCmd As New SqlClient.SqlCommand
sqlCmd.CommandText = sql
sqlCmd.Parameters.Add("@dbParam1", item)
sqlCmd.Parameters.Add("@dbParam2", asf)
sqlCmd.Parameters.Add("@dbParam3", DateAdd(DateInterval.Day, 1, Today))
sqlCmd.Parameters.Add("@dbParam4", Format(Today, "MM/dd/yyyy"))
sqlCmd.Parameters.Add("@dbParam5", DateAdd(DateInterval.Day, 1, Today)) |
Try NET Refactor Free for 30 days or purchase now by clicking Download or Purchase.
New Features are being added to NET Refactor, Version 2, and as they are added, you can download the new features without additional cost.
To view additional detail about NET Refactor, you can view the help file for NET Refactor on-line, by clicking Show Help File.
Top of Page
|
|
