无效的列名"sddd". [英] Invalid column name 'sddd'.

问题描述

大家好:D

我正在尝试自学WEBSERVICE，到目前为止我认为它做的还不错，很不幸，当我尝试从数据库中提取值时，我的WEBSERVICE数据库出现错误，它给我一个运行时错误，提示:无效列名称"sddd"."

这是我的代码的一部分:(由于某种原因，它会在我的所有功能中突然出现)

 功能 IDExist( ByVal  iDNum  As  字符串) As  布尔值
    sqlConn.ConnectionString = connectString
    sqlConn.Open()
     Dim  strSQL  As  字符串 = " & iDNum&  " 
    sqlCMD.Connection = sqlConn
    sqlCMD.CommandText = strSQL
     Dim  i  As  字符串 = sqlCMD.ExecuteScalar
    sqlConn.Close()
     Dim 存在 As  布尔值 = 错误
    如果我<> 什么都没有 然后
        存在= 真
    结束 如果
    返回存在
结束 功能 

预先感谢您，Tsahi.

解决方案

在您的代码中:
Dim strSQL As String = "SELECT IDNum FROM ProgDB WHERE (IDNum = " & iDNum & ")"

iDNum被定义为字符串，因此您应尽可能在sql语句中引用该值.
在不引用此内容的情况下，SQL Server将查找与您的iDNum值相同的名称的列.

像这样:(请注意，在IDNum参数之前和之后添加的单引号)
Dim strSQL As String = "SELECT IDNum FROM ProgDB WHERE (IDNum = ''" & iDNum & "'')"

SQL注入警报！ *红灯闪烁！*

  Dim  strSQL  As  字符串 = " 
sqlCMD.CommandText = strSQL
cmd.Parameters.AddWithValue(" ，iDNum)

这将@myParam替换为iDNum的值. SQL负责其余的工作.因此，如果@myParam是varchar，而您的用户将键入"D''Artagnan"，则查询在您的示例中将失败(尝试在SQL中使用D''Artagnan粘贴该确切的字符串，因为''破坏您的命令).但是，通过使用参数，一切都会顺利进行.作为奖励，您的查询被缓存了，可以重新使用，从而提高性能！由于没有奇怪的字符串连接，因此您的代码看起来更简洁.
这是一个双赢的双赢局面...
索尼因为SQL注入而停了好几天，花费了数百万美元，不要犯同样的错误！
从小鲍比表中学习 [ ^ ] :)

P.S.想到这一点，在此示例中，我不确定您是否应该在带引号或不带引号的情况下说"@myParam"，但是使用参数绝对是正确的方法！

Hello guys :D

I''m Trying to teach myself WEBSERVICE and i think its doing pritty good so far, Unfortunetly i got an Error with my WEBSERVICE Database when i try to extract a value out of my DB it gives me a run time error named: "Invalid column name ''sddd''."

heres a part of my code: (it heppens in all my function for some reason)

Function IDExist(ByVal iDNum As String) As Boolean
    sqlConn.ConnectionString = connectString
    sqlConn.Open()
    Dim strSQL As String = "SELECT IDNum FROM ProgDB WHERE (IDNum = " & iDNum & ")"
    sqlCMD.Connection = sqlConn
    sqlCMD.CommandText = strSQL
    Dim i As String = sqlCMD.ExecuteScalar
    sqlConn.Close()
    Dim exist As Boolean = False
    If i <> Nothing Then
        exist = True
    End If
    Return exist
End Function

Thank You in Advance, Tsahi.

解决方案

In your code:
Dim strSQL As String = "SELECT IDNum FROM ProgDB WHERE (IDNum = " & iDNum & ")"

iDNum is defined as as string, so you should probabaly quote the value in your sql statement.
Without quoting this, the sql server will look for a column named the same as your iDNum value.

Like this: (Note the single quote added before and after teh IDNum parameter)
Dim strSQL As String = "SELECT IDNum FROM ProgDB WHERE (IDNum = ''" & iDNum & "'')"

SQL Injection ALERT! *Red lights flashing!*

Dim strSQL As String = "SELECT IDNum FROM ProgDB WHERE IDNum = @myParam"
sqlCMD.CommandText = strSQL
cmd.Parameters.AddWithValue("@myParam", iDNum)

This replaces @myParam with the value of iDNum. SQL takes care of the rest. So if @myParam is a varchar and your user would have typed "D''Artagnan" your query would have failed in your example (try pasting that exact string with D''Artagnan in SQL, won''t work because the '' breaks your command). However, by using parameters everything goes well. As a bonus your queries are cached an can be re-used boosting performance! And your code looks cleaner since there is no weird string concatenation.
It''s a win win win win situation...
Sony was down for days because of SQL Injection, it cost them millions, don''t make that same mistake!
Learn from little Bobby Tables[^] :)

P.S. Coming to think of it, in this example I am not quite sure if you should say ''@myParam'' with or without the quotes, but using parameters is definitly the way to go!

这篇关于无效的列名"sddd".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！