CODEWITHSUNDEEP
sqlasp.net
BenTen
BenTen

Reputation: 403

How to escape single quotes from SQL query string, only if they exist

I am working on an ASP.NET project, which uses SQL Server for a database. I have a query string I am trying to write in which I add the schema name, which is a variable, into the WHERE clause. Even though it isn't common, sometimes there is a single quote that exists within the schema name (such as Jim's Schema). Since this is a variable that changes, and some of the times quotes will exist, how would I go about escaping the possible single quote that breaks my query when it exists? My query string is as follows:

  var statement = string.Format("SELECT TABLE_NAME FROM INFORMATION.TABLES "
  + "  where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = '{0}'", my.SchemaName);

This query works perfectly fine, as intended, when no single quotes exist. I just need to find a way to escape the quote when it does exist. Thanks!

Upvotes: 1

Views: 722

Answers (1)

WaitingForGuacamole
WaitingForGuacamole

Reputation: 4301

  // doubling your single-quotes should fix this
  var statement = string.Format("SELECT TABLE_NAME FROM INFORMATION.TABLES "
  + "  where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = '{0}'", my.SchemaName.Replace("'", "''");

should answer your immediate need. That said, you should parameterize your query so as to minimize the risk of SQL injection.

Upvotes: 1

Related Questions