CODEWITHSUNDEEP
c#sqlsql-serverstring
Ray Alex
Ray Alex

Reputation: 475

String concatenation with escaped variables

Rather simple question

I've got a SQL string similar to the following:

query = "select * from table_name where name ='"+varName+"' and 
date ='"+varDate+"' order by state desc";

The varName, varDate are defined from a previous select query on another db's table. Their values may have ' : / and other special characters within.

Is there a way either by use of C# or SQL that I can "escape" the contents of the varName, varDate in the above select statement?

I'd prefer not to covert special characters :)

EDIT:

Forgot to include - the setup is MSSQL

Upvotes: 2

Views: 762

Answers (2)

Rich O'Kelly
Rich O'Kelly

Reputation: 41757

You can parameterise your queries using any reasonably mature API. This support is offered in ADO.NET:

string query = "select * from table_name where name = @name and date = @date order by state desc";
using (SqlCommand cmd = new SqlCommand(query))
{
  cmd.Parameters.Add("@name", name);
  cmd.Parameters.Add("@date", date);
  using (SqlDataReader reader = cmd.ExecuteReader())
  { ... }
}

Upvotes: 6

RollRoll
RollRoll

Reputation: 8462

I would keep it compiled in a storead procedure

note: regardless the escape, ur current code is wide open for sql injection

Upvotes: 2

Related Questions