Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to search? Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 10:09 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, December 15, 2014 10:57 PM
Points: 517, Visits: 646
GilaMonster (9/25/2013)
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...


Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time

can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(


Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.

Now, if you want to do a dynamic search, you will need dynamic SQL, but not the very convoluted, insecure way you've got.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Oh, and if you want to know why the way you've written it is bad, try running this:

EXEC [dbo].[SearchBiography] @firstname = 'Gary'
@middlename = ''
@lastname = 'White''; shutdown with nowait --'
@sex = ''
@status = ''
@sexID = 0
@statusID = 0;

Edit: one quote too many



+1 :)
Post #1498655
Posted Thursday, September 26, 2013 3:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
enriquezreyjoseph (9/25/2013)
Hi Sir Gail..

Please see my Attachment..that is the result when i try to run your suggestion...


Look at what the error say and see what I almost did to your server by injecting a command into your dynamic SQL and now ask yourself what it that had been a DROP DATABASE instead of a shutdown which couldn't run?

What would your boss say when you put that into production and someone a little less ethical deletes data, steals your paswords, drops your database, all because you decided that unparameterised dynamic SQL was easier...

Sean, What do you mean i don't have a parameter??


Your dynamic SQL is not parameterised, hence why I could do nasty things just by adding extra commands (that get executed) to the stored procedure parameter value.

If you aren't willing to learn how to write dynamic SQL safely, then please stop writing it at all, for your company's sake and the sake of all their customers. Oh, and stop running your queries as SA too.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498733
Posted Thursday, September 26, 2013 4:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
GilaMonster (9/26/2013)
enriquezreyjoseph (9/25/2013)
Hi Sir Gail..

Please see my Attachment..that is the result when i try to run your suggestion...


Look at what the error say and see what I almost did to your server by injecting a command into your dynamic SQL and now ask yourself what it that had been a DROP DATABASE instead of a shutdown which couldn't run?

What would your boss say when you put that into production and someone a little less ethical deletes data, steals your paswords, drops your database, all because you decided that unparameterised dynamic SQL was easier...

Sean, What do you mean i don't have a parameter??


Your dynamic SQL is not parameterised, hence why I could do nasty things just by adding extra commands (that get executed) to the stored procedure parameter value.

If you aren't willing to learn how to write dynamic SQL safely, then please stop writing it at all, for your company's sake and the sake of all their customers. Oh, and stop running your queries as SA too.


What is SA sir gail??
Post #1498748
Posted Thursday, September 26, 2013 4:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
SysAdmin.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498753
Posted Thursday, September 26, 2013 6:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
GilaMonster (9/26/2013)
SysAdmin.



I'm willing to learn sir gail...that is why i exist in this community because of you...
Post #1499125
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse