Geek Answers Handbook

Is using dynamic SQL safe with parameters? If not, what security issues can be detected?

For example, this is the code I'm using:

String commandString = "UPDATE Members SET UserName = @newName , AdminLevel = @userLevel WHERE UserID = @userid";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnectionstring"].ConnectionString))
{
    SqlCommand cmd = new SqlCommand(commandString, conn);
    cmd.Parameters.Add("@newName", newName);
    cmd.Parameters.Add("@userLevel", userLevel);
    cmd.Parameters.Add("@userid", userid);
    conn.Open();
    cmd.ExecuteReader();
    Reader.Close();
}
+5
source share
3 answers

This code looks good. Parameterization is the path, not the concatenation of user-supplied values, in the adhoc SQL statement, which can open you up to SQL injections. It can also help reuse the execution plan.

, , . , , NVARCHAR VARCHAR. .

+7

SQL-, . , , @userid , , .

+4

. "" sql, — - :

var sql = "SELECT columns FROM Table WHERE 1=1";
if (!string.IsNullOrEmpty(txtName.Text)) sql += " AND Name LIKE '%' + @Name + '%'";
if (!string.IsNullOrEmpty(txtDesc.Text)) sql += " AND CONTAINS(DESCRIPTION, @description)";

But even in this case, it is still “safe” in the sense of SQL injection, as long as you continue to use parameters for each part of the query that arises from user input.

+2
source

More articles:


All Articles