英文:
How to Correctly Parameterize Queries
问题
Here's the translated portion:
我在理解参数化查询方面遇到了一些困难，我认为我至少接近了，但我知道我漏掉了一些东西。这是我的代码：
cmd.CommandText = "UPDATE phoneNumberData Set [Full Name]= @deviceUser, [Phone Number]= @phoneNumber, " +
"[Email]= @email, [Device Type]= @deviceType, [Cost Center]= @costCenter, WHERE [ID]= @id";
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@deviceUser", txtDeviceUser.Text.TrimStart());
param[1] = new SqlParameter("@phoneNumber", txtMobileNumber.Text.TrimStart());
param[2] = new SqlParameter("email", txtEmail.Text.TrimStart());
param[3] = new SqlParameter("@deviceType", txtDeviceType.Text.TrimStart());
param[4] = new SqlParameter("@costCenter", txtCostCenter.Text.TrimStart());
param[5] = new SqlParameter("@id", Convert.ToInt32(txtId.Text.TrimStart()));
cmd.ExecuteNonQuery();
我成功使网站运行而不使用参数，但我决定将那段代码注释掉，改为这样做，以防止 SQL 注入。我遇到了一个未处理的用户异常：>System.Data.SqlClient.SqlException: '必须声明标量变量 "@deviceUser"。'
我已经尝试搜索谷歌并尝试从其他人那里学习如何做到这一点，但我不明白为什么我的似乎不起作用。有人可以告诉我我到底漏掉了什么或者参数化查询是如何工作的，以便我可以解决这个问题吗？
英文:
I am having some trouble understanding parameterizing queries, I though I got it pretty close at least but I know I'm missing something. Here's my code:
cmd.CommandText = "UPDATE phoneNumberData Set [Full Name]= @deviceUser, [Phone Number]= @phoneNumber, " +
"[Email]= @email, [Device Type]= @deviceType, [Cost Center]= @costCenter, WHERE [ID]= @id";
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@deviceUser", txtDeviceUser.Text.TrimStart());
param[1] = new SqlParameter("@phoneNumber", txtMobileNumber.Text.TrimStart());
param[2] = new SqlParameter("email", txtEmail.Text.TrimStart());
param[3] = new SqlParameter("@deviceType", txtDeviceType.Text.TrimStart());
param[4] = new SqlParameter("@costCenter", txtCostCenter.Text.TrimStart());
param[5] = new SqlParameter("@id", Convert.ToInt32(txtId.Text.TrimStart()));
cmd.ExecuteNonQuery();
I was able to get my site to work without using parameters but I decided to comment that code out in place of this so that I could prevent sql injections. I'm getting a user unhandled exception: >System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@deviceUser".'
What I've done is search google and attempt to learn how to do this from other people but I don't see how mine doesn't seem to work. Can anyone tell me what exactly I'm missing or how parameterizing queries work so that I can fix this issue?
答案1
得分: 1
参数必须是cmd变量的Parameters集合的一部分。我还注意到Set列表末尾有多余的逗号。最后，出于性能原因，在许多情况下最好包括参数的类型信息：
cmd.CommandText = @"
UPDATE phoneNumberData
SET [Full Name]= @deviceUser, [Phone Number]= @phoneNumber,
[Email]= @email, [Device Type]= @deviceType, [Cost Center]= @costCenter
WHERE [ID]= @id";
// 这里我必须猜测类型信息。
// 使用数据库中的确切类型和长度。
cmd.Parameters.Add("@deviceUser", SqlDbType.NVarChar, 20).Value = txtDeviceUser.Text.TrimStart();
cmd.Parameters.Add("@phoneNumber", SqlDbType.VarChar, 12).Value = txtMobileNumber.Text.TrimStart();
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 55).Value = txtEmail.Text.TrimStart();
cmd.Parameters.Add("@deviceType", SqlDbType.VarChar, 15).Value = txtDeviceType.Text.TrimStart();
cmd.Parameters.Add("@costCenter", SqlDbType.VarChar, 12).Value = ChartxtCostCenter.Text.TrimStart();
cmd.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(txtId.Text.TrimStart());
英文:
The parameters have to be part of the cmd variable's Parameters collection. I also noticed an extra comma at the end of the Set list. Finally, for performance reasons, you will do better in many cases if you include the type information for the parameters:
cmd.CommandText = @"
UPDATE phoneNumberData
SET [Full Name]= @deviceUser, [Phone Number]= @phoneNumber,
[Email]= @email, [Device Type]= @deviceType, [Cost Center]= @costCenter
WHERE [ID]= @id";
// I have to guess at type information here.
// Use the exact types and lengths from the database.
cmd.Parameters.Add("@deviceUser", SqlDbType.NVarChar, 20).Value = txtDeviceUser.Text.TrimStart();
cmd.Parameters.Add("@phoneNumber", SqlDbType.VarChar, 12).Value = txtMobileNumber.Text.TrimStart();
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 55).Value = txtEmail.Text.TrimStart();
cmd.Parameters.Add("@deviceType", SqlDbType.VarChar, 15).Value = txtDeviceType.Text.TrimStart();
cmd.Parameters.Add("@costCenter", SqlDbType.VarChar, 12).Value = ChartxtCostCenter.Text.TrimStart();
cmd.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(txtId.Text.TrimStart());
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论