How to pass a single quote ( ' ) from user input to a stored procedure, avoiding SQL injection

Question

Our application takes a user's name as input from the frontend, and sometimes the name has a single quote. So how do I pass the name entered to a stored procedure if the name contains a single quote (')?

We can not pass a single quote (') directly to stored procedure input, as it may cause SQL injection.

Database Used: SQL Server 2008
Application: ASP.NET C# 4.0

Answer 1

Use parameterized queries. For instance,

var command = new SqlCommand("select * from person where firstname = @firstname");
SqlParameter parameter  = new SqlParameter();
parameter.ParameterName = "@firstname";
parameter.Value= "D'Jork";
command.Parameters.Add(parameter);

Answer 2

You can use parameterized query.Single quotes are handled by them.

You can read in depth about it at: Link

Answer 3

You need to double it up:

INSERT INTO myTable(text) VALUES ('I don''t like this!')

But as the previous poster has stated... using parameters is better because it is safer.

Answer 4

You can use SqlCommandBuilder.QuoteIdentifier(/* params */):

var sqlCommandBuilder = new SqlCommandBuilder();
var sqlCommand = string.Format("EXEC dbo.SomeProcedure '{0}'",
                         sqlCommandBuilder.QuoteIdentifier("Jack's name"));

// futher sql command process

More on msdn.

Answer 5

Although replacing single quote with double quote before calling stored procedure should also work, something like this will also work:

@parameter = 'Test'' A''B''C' -- Test' A'B'C

But if you don't want to go this way, you can also try with

Use SET QUOTED_IDENTIFIER OFF

just before hitting the query that will also work, but remember we need QUOTED_IDENTIFIER ON for many situations.