Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 dynamic sql-2 questions

Author  Topic 

bill_
Starting Member

38 Posts

Posted - 2013-09-17 : 12:07:24
I read that executing a query string isn't safe when parameters come from screen input.

1) Is that because parameters can be wrong and cause errors and because they can be used for injection ?
2) If those parameters are checked to make sure they're ok, is executing a query string safe ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-17 : 12:32:45
quote:
Originally posted by bill_

I read that executing a query string isn't safe when parameters come from screen input.

1) Is that because parameters can be wrong and cause errors and because they can be used for injection ?
2) If those parameters are checked to make sure they're ok, is executing a query string safe ?


The safety issue comes from potential sql injection attacks. In addition to safety, it also has performance and logical considerations. For example:

1. Inability to reuse query plans
2. Breaking of ownership chains
3. More complex and less readable code etc.

But above all, it is really the risk of SQL injection attacks that makes it undesirable to use dynamic SQL. If you do want to use dynamic sql, parameterize your queries and use sp_executesql rather than exec.
Go to Top of Page
   

- Advertisement -