Excellent stored procedure
-
I had to go back and look at how the product search was working on an old classic asp application. I happened to find this little gem lurking in the database. Fortunately there is some sanitizing going on in the asp to prevent a sql injection but a little piece of me died when I saw it.
ALTER procedure [dbo].[ily_products_search_keyword] (@sWhere varchar(2000) ) as set nocount on declare @sql varchar(8000) select @sql = 'SELECT products.product_id,products.product_name,products.file_url, ' select @sql = @sql + 'products.print_url, products.product_desc ' select @sql = @sql + ' FROM products ' select @sql = @sql + ' WHERE products.active = 1 ' select @sql = @sql + @sWhere select @sql = @sql + ' ORDER BY products.product_name ' exec(@sql) -
I had to go back and look at how the product search was working on an old classic asp application. I happened to find this little gem lurking in the database. Fortunately there is some sanitizing going on in the asp to prevent a sql injection but a little piece of me died when I saw it.
ALTER procedure [dbo].[ily_products_search_keyword] (@sWhere varchar(2000) ) as set nocount on declare @sql varchar(8000) select @sql = 'SELECT products.product_id,products.product_name,products.file_url, ' select @sql = @sql + 'products.print_url, products.product_desc ' select @sql = @sql + ' FROM products ' select @sql = @sql + ' WHERE products.active = 1 ' select @sql = @sql + @sWhere select @sql = @sql + ' ORDER BY products.product_name ' exec(@sql)Agile development in it's purest form :p
xacc.ide
The rule of three: "The first time you notice something that might repeat, don't generalize it. The second time the situation occurs, develop in a similar fashion -- possibly even copy/paste -- but don't generalize yet. On the third time, look to generalize the approach." -
I had to go back and look at how the product search was working on an old classic asp application. I happened to find this little gem lurking in the database. Fortunately there is some sanitizing going on in the asp to prevent a sql injection but a little piece of me died when I saw it.
ALTER procedure [dbo].[ily_products_search_keyword] (@sWhere varchar(2000) ) as set nocount on declare @sql varchar(8000) select @sql = 'SELECT products.product_id,products.product_name,products.file_url, ' select @sql = @sql + 'products.print_url, products.product_desc ' select @sql = @sql + ' FROM products ' select @sql = @sql + ' WHERE products.active = 1 ' select @sql = @sql + @sWhere select @sql = @sql + ' ORDER BY products.product_name ' exec(@sql)eggsovereasy wrote:
ALTER procedure [dbo].[ily_products_search_keyword] (@sWhere varchar(2000) ) as set nocount on declare @sql varchar(8000) select @sql = 'SELECT products.product_id,products.product_name,products.file_url, ' select @sql = @sql + 'products.print_url, products.product_desc ' select @sql = @sql + ' FROM products ' select @sql = @sql + ' WHERE products.active = 1 ' select @sql = @sql + @sWhere select @sql = @sql + ' ORDER BY products.product_name ' exec(@sql)
I wonder what will happen if I try the following:
exec [dbo].[ily_products_search_keyword] ' delete from products select * from products'
:)
-
eggsovereasy wrote:
ALTER procedure [dbo].[ily_products_search_keyword] (@sWhere varchar(2000) ) as set nocount on declare @sql varchar(8000) select @sql = 'SELECT products.product_id,products.product_name,products.file_url, ' select @sql = @sql + 'products.print_url, products.product_desc ' select @sql = @sql + ' FROM products ' select @sql = @sql + ' WHERE products.active = 1 ' select @sql = @sql + @sWhere select @sql = @sql + ' ORDER BY products.product_name ' exec(@sql)
I wonder what will happen if I try the following:
exec [dbo].[ily_products_search_keyword] ' delete from products select * from products'
:)
You need a ";--" at the end because of the order by, but yeah, its pretty bad.
-
You need a ";--" at the end because of the order by, but yeah, its pretty bad.
eggsovereasy wrote:
You need a ";--" at the end because of the order by
Not really, the "order by" was taken care of by the preceding "select * ", although it is unnecessary since no row will be returned. :)
-
I had to go back and look at how the product search was working on an old classic asp application. I happened to find this little gem lurking in the database. Fortunately there is some sanitizing going on in the asp to prevent a sql injection but a little piece of me died when I saw it.
ALTER procedure [dbo].[ily_products_search_keyword] (@sWhere varchar(2000) ) as set nocount on declare @sql varchar(8000) select @sql = 'SELECT products.product_id,products.product_name,products.file_url, ' select @sql = @sql + 'products.print_url, products.product_desc ' select @sql = @sql + ' FROM products ' select @sql = @sql + ' WHERE products.active = 1 ' select @sql = @sql + @sWhere select @sql = @sql + ' ORDER BY products.product_name ' exec(@sql)The code is optimized for "readability" and "maintability", so everybody understands what is going on. Maybe the compiler does the rest of the work via detecting the first four @sql string statements are constants, and make one string of them.:cool: The sql varchar(8000) seems to be a little to big. But better too big than to short. X| And a clever compiler knows that the constants strings and 2000 are less than 8000. I dont know how clever a compiler of "today" is. :confused:
Greetings from Germany