From the course: SQL Server: Security for Developers

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Demo: Stored procedures vs. dynamic SQL

Demo: Stored procedures vs. dynamic SQL - SQL Server Tutorial

From the course: SQL Server: Security for Developers

Start my 1-month free trial

Demo: Stored procedures vs. dynamic SQL

“

- [Instructor] Here we're gonna use an example. We're gonna do a stored procedure and boast some dynamic sequel. Both of these are kind of equally secure in this scenario. So we're doing good things. Here, we have our input parameters and they're typed so we are eliminating name to Varchar 50. Obviously, we have to kind of do that, from a product perspective, 'cause our product name column is that wide. 50 characters is going to be hard to get a strong sequel injection command in to. Ideally, we'd like to type that even more, so that we're only passing in a number. So maybe we would do Select Product ID, or pass the name and do a look-up. But, they're a couple reasons why I really like to do this as a stored procedure. Number one is that, you only have to grant execute access to the stored procedure and you don't have to grant the underlying direct object access. And then two, from a performance perspective and a manageability perspective, you're gonna get better code reuse and better…

Contents