如何在asp.net中使用COUNT函数和sql [英] How to use COUNT funcation in asp .net with sql

问题描述

string select = "select COUNT(Bookid) from book1 where Regid='" + TextBox8 + "'";

推荐答案

您的代码容易受到 SQL Injection [ ^ ]。



从不使用字符串连接来构建SQL查询。 总是使用参数化查询。



一旦你修复了这个漏洞，你就会发现你正试图通过 TextBox 控制查询，而不是 .Text 属性。

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Once you've fixed that vulnerability, you'll see that you're trying to pass the TextBox control to the query, rather than its .Text property.

using (var connection = new SqlConnection("YOUR CONNECTION STRING"))
using (var command = new SqlCommand("SELECT Count(BookId) FROM book1 WHERE RegId = @RegId"))
{
    command.Parameters.AddWithValue("@RegId", TextBox8.Text);
    
    connection.Open();
    int count = Convert.ToInt32(cmd.ExecuteScalar());
    lblCount.Text = count.ToString();
}

使用SqlCommand对象。由于只返回1个值，您可以使用ExecuteScalar（）。

Use a SqlCommand object. And since there is only 1 value being returned you can use ExecuteScalar().

...
cmd.CommandText = select;
Int32 count = Int32.Parse(cmd.ExecuteScalar().ToString());
lblCount.Text = count.ToString();
...

此外，正如Richard在解决方案2中提到的那样，更改SQL以便您没有Sql注入问题。

Also, as Richard mentions in Solution 2, change your SQL so that you do not have Sql injection issues.

你的方法从一开始就是错误的。您的查询是通过连接从UI获取的字符串组成的。不仅重复的字符串连接是低效的（因为字符串是不可变的;我是否必须解释为什么它会使重复连接变坏？），但是有更重要的问题：它打开了通向良好的大门已知的漏洞称为 SQL注入。



这是它的工作原理： http://xkcd.com/327 。



用户可以在UI元素中添加任何内容，包括...... SQL代码片段。你明白了吗？



怎么办？只需阅读有关此问题和主要补救措施：参数化语句： http://en.wikipedia.org/ wiki / SQL_injection 。



使用ADO.NET，使用：http://msdn.microsoft.com/en-us/library/ff648339.aspx 。



请参阅我过去的答案有更多细节：

在com.ExecuteNonQuery中更新EROR（ ）; ，

嗨名字没有显示在名称中？。

-SA

Your approach is wrong from the very beginning. Your query is composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

The user can add anything in UI elements, including… the fragment of SQL code. Are you getting the idea?

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

—SA

这篇关于如何在asp.net中使用COUNT函数和sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！