I was looking at an old stored procedure today and noticed it was using
quotename on the input parameters. After doing some digging to figure out what that does exactly I came across this site. I now understand what it does and how to use it but the site says it is used as a mitigation from SQL Injection attacks. When I used to develop apps that directly queried a database, using asp.net, I would use ADO.Net parameters to pass user input in as a literal value and never really worried about protecting it in my stored procedures.
I am now writing a stored procedure that will be used by applications that I do not write so I do need to try and protect from injection attacks at the procedure level, is the
quotename the best way to do this or is there a newer function/better method?
Code that got me on this thought pattern (
@parm1 is a user input parameter):
'SELECT project [Project], project_desc [Description], customer [Customer], cpnyid [Company] FROM PJPROJ (nolock) where project like ' + quotename(@parm1,'''') + '