语法错误使用数据库登录系统 [英] Syntax Error with Database Login System
问题描述
创建了一个网站登录系统，但是当我运行它，它使想出这个错误以下编码的特定位。是否有人可以帮助我这个错误消息在这个特别的code：
System.Data.SqlClient.SqlException：关键字附近有语法错误
用户。code：
公共静态用户LoginUser（字符串登录，串密码）
{
//检查用户是否存在
查询字符串=的String.Format（SELECT COUNT（*）FROM TeesDB.dbo.user其中name ='{0}'，
登录）;
command.CommandText =查询; 尝试
{
conn.Open（）;
INT amountOfUsers =（int）的command.ExecuteScalar（）; 如果（amountOfUsers == 1）
{
//用户存在，检查密码匹配
查询=的String.Format（选择密码从用户其中name ={0}，登录）;
command.CommandText =查询;
字符串DBPASSWORD = command.ExecuteScalar（）的ToString（）。
如果（DBPASSWORD ==密码）
{
//密码匹配。登录名和密码的数据为我们所知。
//从数据库中检索用户的进一步数据
查询=的String.Format（选择电子邮件，从USER_TYPE WHERE用户名=
{0}'，登录）;
command.CommandText =查询; SqlDataReader的读者= Command.ExecuteReader却（）;
用户的用户= NULL; 而（reader.Read（））
{
字符串email = reader.GetString（0）;
字符串类型= reader.GetString（1）; 用户=新用户（登录名，密码，电子邮件型）;
}
返回用户;
}
其他
{ //密码不匹配
返回null; } }
其他
{ //用户存在
返回null; }
}
最后
{ conn.Close（）;
}
}
}
}
解决方案这是因为用户是一个保留关键字。要提到它在查询你需要封装方括号中的字
查询字符串=的String.Format（SELECT COUNT（*）FROM [用户]其中name = .....但在这一点上，为什么在后续查询使用名称
用户？它只是在第一个查询，或在接下来的一个错字？不过，你也应该记住，字符串格式化你的查询以这种方式很不好。结果
你是在SQL注入风险，如果一个单引号出现在文本值，则整个查询将在一个无效的语法resul作为一个参数化查询的例子来取代您的查询
查询字符串=SELECT COUNT（*）FROM [用户]其中name = @name
command.CommandText =查询;
command.Parameters.AddWithValue（@名，登录）;
INT amountOfUsers =（Convert.ToInt32（command.ExecuteScalar（））;
如果（amountOfUsers大于0）
{
.....
}那么接下来的问题是，从数据库中检索，并与用户输入进行比较的口令。有了这种code意味着密码存储在数据库里明文。一个明显的安全隐患。你应该以加密的形式存储密码。当你需要检查用户凭据将相同crypting算法对用户的输入，并检查对数据库中的密码。
您可以在单个操作中执行此
字符串cryptPwd = EncryptPassword（密码）;
查询字符串=SELECT COUNT（*）FROM [用户]其中name = @名称和密码= @cryptpwd
....Created a website login system but when I run it it keeps coming up with this error to the particular bit of coding below. Can someone PLEASE help me with this Error message on this particular code:
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'user'.
Code:
public static User LoginUser(string login, string password) { //Check if user exists string query = string.Format("SELECT COUNT(*) FROM TeesDB.dbo.user WHERE name = '{0}'", login); command.CommandText = query; try { conn.Open(); int amountOfUsers = (int) command.ExecuteScalar(); if(amountOfUsers == 1) { //User exists, check if the password match query = string.Format("SELECT password FROM users WHERE name = '{0}", login); command.CommandText = query; string dbPassword = command.ExecuteScalar().ToString(); if (dbPassword == password) { //password match. Login and password data are known to us. //retrieve further user data from the database query = string.Format("SELECT email, user_type FROM users WHERE name = '{0}'", login); command.CommandText = query; SqlDataReader reader = command.ExecuteReader(); User user = null; while (reader.Read()) { string email = reader.GetString(0); string type = reader.GetString(1); user = new User(login, password, email, type); } return user; } else { //passwords do not match return null; } } else { //user exists return null; } } finally { conn.Close(); } } } }
解决方案That happens because USER is a reserved keyword. To refer to it in your queries you need to encapsulate the word between square brackets
string query = string.Format("SELECT COUNT(*) FROM [user] WHERE name = .....but at this point, why in subsequent queries you use the name
users? It is just a typo in the first query or in the next?However, you should also keep in mind that string formatting your queries in that way is very bad.
You are at risk of Sql Injections and, if a single quote appears in your text values, the whole query will resul in an invalid syntaxAs an example of a parameterized query to replace your query
string query = "SELECT COUNT(*) FROM [user] WHERE name = @name", command.CommandText = query; command.Parameters.AddWithValue("@name",login); int amountOfUsers = (Convert.ToInt32(command.ExecuteScalar()); if(amountOfUsers > 0) { ..... }Then the next problem is the password retrieved from the database and compared with the user input. Having this kind of code means the password is stored in clear text inside the database. A clear security risk. You should store passwords in an encrypted form. When you need to check the user credentials you apply the same crypting algorithm to the user input and checks against the password in the database.
You could do this in a single operation
string cryptPwd = EncryptPassword(password); string query = "SELECT COUNT(*) FROM [user] WHERE name = @name and password = @cryptpwd", ....
这篇关于语法错误使用数据库登录系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
