SqlCe 参数错误

发布于 2024-11-26 14:07:17 字数 1396 浏览 3 评论 0原文

我在这个可爱的星球上进行了许多参数化查询，但没有一个抛出这样的错误......WTFudge？！？！

错误：

There was an error parsing the query. [
Token line number = 1,
Token line offset = 20,
Token in error = @table ]

显然编译器不喜欢我的 SQL 语句...但我看不出有问题？？？

这是我的代码。

using (SqlCeConnection con = new SqlCeConnection(_connection))
{
    string sqlString = "SELECT @colID FROM @table WHERE @keyCol = @key";

    SqlCeCommand cmd = new SqlCeCommand(sqlString, con);
    cmd.Parameters.Add(new SqlCeParameter("@table", tableName));
    cmd.Parameters.Add(new SqlCeParameter("@colID", columnIdName));
    cmd.Parameters.Add(new SqlCeParameter("@keyCol", keyColumnName));
    cmd.Parameters.Add(new SqlCeParameter("@key", key));

    try
    {
        con.Open();
        return cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
        Console.Write(ex.Message);
        throw new System.InvalidOperationException("Invalid Read. Are You Sure The Record Exists", ex);
    }
    finally
    {
        if (con.State == ConnectionState.Open)
            con.Close();
        cmd.Dispose();
        GC.Collect();
    }
}

正如你所看到的，这是一个非常简单的 SQL 语句。我虽然“@table”可能被愚蠢地保留了或者其他什么......所以我尝试了@tableName，@var，@everything！不知道问题是什么。

在调试过程中，我检查了 SqlCeParameterCollection 中实际上有一个 @table 参数，而且它就在那里。晴如白昼！！

图片：调试信息

原文

I have made MANY parameterised queries in my time on this lovely planet, and none have thrown an error like this... WTFudge?!?!

ERROR:

There was an error parsing the query. [
Token line number = 1,
Token line offset = 20,
Token in error = @table ]

Obviously the compiler doesn't like my SQL statement... but I see no problem???

Here is my code.

using (SqlCeConnection con = new SqlCeConnection(_connection))
{
    string sqlString = "SELECT @colID FROM @table WHERE @keyCol = @key";

    SqlCeCommand cmd = new SqlCeCommand(sqlString, con);
    cmd.Parameters.Add(new SqlCeParameter("@table", tableName));
    cmd.Parameters.Add(new SqlCeParameter("@colID", columnIdName));
    cmd.Parameters.Add(new SqlCeParameter("@keyCol", keyColumnName));
    cmd.Parameters.Add(new SqlCeParameter("@key", key));

    try
    {
        con.Open();
        return cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
        Console.Write(ex.Message);
        throw new System.InvalidOperationException("Invalid Read. Are You Sure The Record Exists", ex);
    }
    finally
    {
        if (con.State == ConnectionState.Open)
            con.Close();
        cmd.Dispose();
        GC.Collect();
    }
}

as you can see its a VERY simple SQL statement. I though "@table" may have been stupidly reserved or something... so ive tried @tableName, @var, @everything!!! dont know what the problem is.

During debug I checked that there was actually a @table parameter in the SqlCeParameterCollection And it was there. Clear as day!!

Image: Debug Information

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄与心 2024-12-03 14:07:17

由于您使用的是 C#（而不是存储过程），

string sqlString = "SELECT " + columnIdName + 
" FROM " +tableName "WHERE " + keyColumnName + "= @key";

因此您需要验证 columnIdName、tableName、keyColumnName 是否都限制为值列表（或者至少将长度限制为 50 个字符），否则会出现此情况过程针对不安全性和 SQL 注入攻击进行了优化。

Since you are in C# (as opposed to stored procs)

string sqlString = "SELECT " + columnIdName + 
" FROM " +tableName "WHERE " + keyColumnName + "= @key";

You will want to verify that columnIdName, tableName, keyColumnName are all restricted to a list of values (or at the very least, restrict the length to, say 50 characters), otherwise this procedure is optimized for insecurity and sql injection attacks.

回复收藏 0 原文