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...
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
}
}
}
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
