0

For insertion I am already using parametrized query:

cmd.Parameters.Add("@ParamName",SqlDbType.VarChar).Value = objCampaignType.Name;

I have a SQL query to search data from search text

SELECT p.Name, c.Name
FROM Person AS p 
INNER JOIN Country AS c ON p.Country = c.ID
WHERE p.Name LIKE '%searchText%' AND c.Name = USA

How do I use parametrized query to prevent SQL injection using C#?

I am using SQL Server 2008 and .Net C#

Thanks in advance...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
yohan.jayarathna
  • 3,423
  • 13
  • 56
  • 74

2 Answers2

1

You will need to use system stored procedure sp_executesql and pass parameters to that procedure something like this....

DECLARE @Sql NVARCHAR(MAX);
DECLARE @Search NVARCHAR(100) = 'Searchme';


SET @Sql = N' SELECT     p.Name, c.Name ' +
           N' FROM  Person AS p INNER JOIN Country AS c ON p.Country = c.ID ' +
           N' WHERE  p.Name LIKE ''%@Search%'' AND c.Name = USA'

EXECUTE sp_executesql @Sql
                     ,N'@Search NVARCHAR(100)'
                     ,@Search
M.Ali
  • 67,945
  • 13
  • 101
  • 127
1
  using (var conn = new SqlConnection(connectionString)) {
    var query = @"
SELECT     p.Name, c.Name
FROM         Person AS p INNER JOIN
                  Country AS c ON p.Country = c.ID
WHERE  p.Name LIKE '%' + @SearchText + '%' AND c.Name = @CountryName";
    var cmd = new SqlCommand(query, conn);
    cmd.Parameters.Add("SearchText", System.Data.SqlDbType.VarChar, 50).Value = "search text";
    cmd.Parameters.Add("CountryName", System.Data.SqlDbType.VarChar, 50).Value = "USA";
    conn.Open();
    using (var reader = cmd.ExecuteReader()) {
      while (reader.Read()) {
        // enjoy dataset
      }
    }
  }
Ondrej Svejdar
  • 21,349
  • 5
  • 54
  • 89