escape apostrophes in Linq based stored procedure calls when SP contains dynamic SQL

Question

Hi guys,

I noticed the following:

An ASP.NET MVC website under development gets an SQL error "Unclosed quotation mark ..." when it makes a LINQ call to a stored procedure that contains dynamic SQL.

For example:

SP GetEmployees called with parameter [filter_name] that has value [n'for] throws this error

I can fix the problem by doing a .replace("'", "''") like this:

[Function(Name = "dbo.GetEmployees")]
public ISingleResult<EmployeeRow> GetEmployees(
            [Parameter(DbType = "NVarChar(MAX)")] string filter_name)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), filter_name.Replace("'", "''"));
    return ((ISingleResult<EmployeeRow>)(result.ReturnValue));
}

Now, I don't feel like going trough all my SPs and doing this manually. Is there a way to make this a general rule that should be applied to all Linq SP calls I have now and will make in the future?

Also, is there something else I should be escaping to prevent SQL injection attacks?

EDIT:

Added question: Will this give problems with SPs that dont include dynamic sql? I mean, when I add that name in the database, will it be stored as [n''for]? I just realized this will probably be the case and then I'll have to do in manually anyway

Answer 1

I'm going to give a (possible) answer here to my own question. (let me know in comments if you agree)

It seems more correct that this should be handled inside the SP. The application should not have to worry about wether a certain SP contains dynamic sql or not.

Answer 2

I suggest you move away from dynamic SQL, as that's the root of the problem. (I know this might cause lots of other issues though, and might not be possible.)

Unless you can guarantee that the dynamic SQL you're building is safe, (so you control this logic internally, with nothing being passed through from the user), it's going to be a problem.

What would happen if filter_name contained a \' or --?