daninmanchester
08-27-2006, 03:24 PM
this may have been mentioned already but I'm posting in reference to :
http://www.codingforums.com/showthread.php?t=9843
Whilst what whammy suggests is correct it isn't always the best solution the two main reasons being outlined below :
Dynamic SQL and user input
the problem with building dynamic SQL in this way is that it is open to attack as the user input goes pretty much straight to the SQL server with little or no sanitation. Of course you could develop whammy's approach to better santitise user input and provide validation but there are better ways.
Database performance
for every SQL statement you have a potentially unique SQL command. Lets take a simple search example where the user can enter one or more words. This example has a potentially infinite number of user entries.
The solution
Always use commands with parameterised queries. This removes the need for you to manually sanatise your userinput to protect your scripts from attack. Having said that this is not a replacement for good input validation.
Secondly If you use parameters we immediatly reduce the number of SQL commands we are using because before your database server was seeing :
SELECT * FROM myTable WHERE value = 'eggs'
SELECT * FROM myTable WHERE value = 'apples'
SELECT * FROM myTable WHERE value = 'bread'
it now sees :
SELECT * FROM myTable WHERE value = @InputParameter
and so it can define one execution plan, cache it for that particular query and reuse it regardless of the input parameter.
You can learn more about using ADO commands here
http://www.w3schools.com/ado/ado_ref_command.asp
http://www.devguru.com/technologies/ado/8516.asp
http://www.codingforums.com/showthread.php?t=9843
Whilst what whammy suggests is correct it isn't always the best solution the two main reasons being outlined below :
Dynamic SQL and user input
the problem with building dynamic SQL in this way is that it is open to attack as the user input goes pretty much straight to the SQL server with little or no sanitation. Of course you could develop whammy's approach to better santitise user input and provide validation but there are better ways.
Database performance
for every SQL statement you have a potentially unique SQL command. Lets take a simple search example where the user can enter one or more words. This example has a potentially infinite number of user entries.
The solution
Always use commands with parameterised queries. This removes the need for you to manually sanatise your userinput to protect your scripts from attack. Having said that this is not a replacement for good input validation.
Secondly If you use parameters we immediatly reduce the number of SQL commands we are using because before your database server was seeing :
SELECT * FROM myTable WHERE value = 'eggs'
SELECT * FROM myTable WHERE value = 'apples'
SELECT * FROM myTable WHERE value = 'bread'
it now sees :
SELECT * FROM myTable WHERE value = @InputParameter
and so it can define one execution plan, cache it for that particular query and reuse it regardless of the input parameter.
You can learn more about using ADO commands here
http://www.w3schools.com/ado/ado_ref_command.asp
http://www.devguru.com/technologies/ado/8516.asp