是什么导致了这个语法错误?oledb 插入语句 [英] What's causing this syntax error? oledb INSERT statement

问题描述

我发布了一个我之前遇到的不同问题，并在一些帮助下解决了这个问题.

我现在收到一个以以下代码为中心的语法错误:

importConnection.Open();Console.WriteLine("Foxpro 连接打开");OleDbCommand deleteOleDbCommand = new OleDbCommand(@"TRUNCATE TABLE CLIENT",导入连接);Console.WriteLine("写入表格");Console.ReadKey();使用 (OleDbCommand importCommand = new OleDbCommand(string.Format("INSERT INTO CLIENT (Title,Fname,Sname)" + "VALUES ({0},{1},{2})",exportReader.GetValue(0)、exportReader.GetValue(1)、exportReader.GetValue(2))))importCommand.ExecuteReader();

输出如下:

输出文本

使用断点我已经确定导出读取器调用正在接收数据.我已将其缩小为以下问题:

"VALUES ({0},{1},{2})",exportReader.GetValue(0), exportReader.GetValue(1), exportReader.GetValue(2))))

我已经确认可以通过执行以下操作来插入数据:

using (OleDbCommand importCommand =新的 OleDbCommand(string.Format(插入客户端(标题，Fname，Sname)"+值(Mr，Joshua，Cameron-Mackintosh)"，导入连接)))

这不会造成任何问题，所以我知道问题不在于底层连接或命令.

解决方案

其他人对 SQL-Injection 的评论是正确的，但是 VFP 对 SQL-Injection 的影响较小，它可以是一样的.主要原因，VFP 并不像其他 sql 引擎允许的;"那样真正处理多个查询.识别语句之间的中断.但是，如果引号不匹配，它会并且会破坏您的 sql 语句的实际运行.

话虽如此，VFP OleDb 提供程序确实允许参数化，但没有命名"参数.它用?"作为 .net 框架插入值的位置的占位符，并且您不必转换数据类型，只要它采用相同的预期格式(例如:字符串、数字、日期)

将您的 OleDbCommand 更改为

INSERT INTO CLIENT (Title, Fname, Sname) values (?, ?, ? )"

然后，通过

设置参数

importCommand.Parameters.Add("parmForTitle", exportReader.GetValue(0));importCommand.Parameters.Add("parmForFName", exportReader.GetValue(1));importCommand.Parameters.Add("parmForSName", exportReader.GetValue(2));

此外，参数必须按照它们在查询中出现的完全相同的顺序添加.因此，我在它们前面加上了parmFor"前缀，以表明它是插入(或更新，或在选择、插入或删除中也使用)的相应字段的参数位置.命令对象对所有命令的工作方式相同.即使您编写了一个 select 语句并且在 WHERE、JOIN 或任何其他位置具有值.

然后，ExecuteNonQuery()

I posted about a different issue I had earlier, and with some help resolved that.

I am now receiving a syntax error centered around the following code:

importConnection.Open();
Console.WriteLine("Foxpro connection open");
OleDbCommand deleteOleDbCommand = new OleDbCommand(@"TRUNCATE TABLE CLIENT",
                            importConnection);

Console.WriteLine("writing to table");
Console.ReadKey();
using (OleDbCommand importCommand = new OleDbCommand(
    string.Format("INSERT INTO CLIENT (Title,Fname,Sname)" + "VALUES ({0},{1},{2})",
    exportReader.GetValue(0), exportReader.GetValue(1), exportReader.GetValue(2))))
    importCommand.ExecuteReader();

With this being the output:

Output Text

using break points I have determined that the export reader calls are receiving data. I've narrowed it down to an issue with:

"VALUES ({0},{1},{2})",
  exportReader.GetValue(0), exportReader.GetValue(1), exportReader.GetValue(2))))

I have confirmed that data can be inserted by doing the following:

using (OleDbCommand importCommand =
    new OleDbCommand(string.Format(
       "INSERT INTO CLIENT (Title,Fname,Sname)" + "VALUES (Mr,Joshua,Cameron-Mackintosh)",
           importConnection)))

This causes no problems, so I know the issue does not lie with the underlying connection or command.

解决方案

Others correctly comment about SQL-Injection, however VFP is less impacted to SQL-Injection, it can be just the same. Main reason, VFP doesn't really work with multiple queries the same way other sql engines allow by a ";" identifying break between statements. However, with mismatched quotes, it CAN and WILL break your sql-statements from actually running.

Having said that, VFP OleDb provider does allow parameterizing, but does so without "named" parameters. It does it with "?" as a place-holder for where the value would be inserted by the .net framework, and you don't have to convert the data type as long as it is in the same expected format (ex: string, numeric, date)

change your OleDbCommand to

"INSERT INTO CLIENT (Title, Fname, Sname ) values ( ?, ?, ? )"

Then, set your parameters via

importCommand.Parameters.Add( "parmForTitle", exportReader.GetValue(0));
importCommand.Parameters.Add( "parmForFName", exportReader.GetValue(1));
importCommand.Parameters.Add( "parmForSName", exportReader.GetValue(2));

Also, the parameters must be added in the exact same sequential order as they appear in the query. So, I prefixed them with "parmFor" to indicate it is the parameter placement for the corresponding field being inserted (or updated, or used in select, insert or delete too). The command objects work the same for all the commands. Even if you write a select statement and have values in a WHERE, JOIN or whatever other position.

THEN, ExecuteNonQuery()

这篇关于是什么导致了这个语法错误?oledb 插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！