我遇到SQL查询问题我试图将文件保存到访问数据库但是运行时出错 [英] I am having a problem with SQL query I am trying to save a file into access database but getting run time error

问题描述

运行时错误' - 2147217900（80040e14）'：查询表达式中出现语法错误（缺少运算符）我收到此错误



我有什么尝试过：



这就是我尝试过的，我在INSERT查询中收到错误

  Dim  fd  As  FileDialog 
  Dim  SelectFile  As   Variant  
  Dim  cnn  As  ADODB.Connection 
  Set  cnn = 新 ADODB.Connection 
  Dim  rst  As  ADODB.Recordset 
 设置 rst = 新 ADODB.Recordset 
 设置 fd = Application.FileDialog（msoFileDialogOpen）
 使用 fd 
 .AllowMultiSelect =  False  
。标题=  请选择要附加的文件 
  If  .Show =  True  然后 
 SelectFile = .SelectedItems （ 1 ）
 否则 
 退出  Sub  
 结束 如果 
 结束 使用 
 设置 fd =  Nothing  
 
 cnn.ConnectionString =   Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& ThisWorkbook.Path&   \ SaveFile.accdb; Persist Security Info = False; 
 cnn.Open 
 设置 rst = cnn.Execute（  INSERT INTO表1（AddFiles）VALUES（& SelectFile&  ））
 '  rst.AddNew  
 rst.Fields（ 1 ）。值=   AddFiles 
 
 rst.Update 
 rst.Close 
 cnn.Close 
 

解决方案

这是一个SQL注入：您的文件名被提升为SQL代码，SQL服务器尝试将文件名理解为某些SQL代码。

第一步是更改为：

 设置 rst = cnn.Execute（  INSERT INTO Table1（AddFiles）VALUES（'& SelectFile&  '））

它将会处理没有撇号的文件名。如果一个文件名可以有一个撇号，问题是一样的，你需要阅读以下文章的真正解决方案。

----

永远不要建立一个SQL查询连接字符串。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

尝试使用 CreateParameter Method（ADO）| Microsoft Docs [ ^ ]为insert语句创建一个新参数。也可以使用执行方法（ADO指挥）| Microsoft Docs [ ^ ]运行INSERT语句。



有关更详细的示例，请使用查看使用ADODB.Command和ADODB.Parameters对象的SQL INSERT / UPDATE示例 - Access World论坛 [ ^

Run time error '- 2147217900(80040e14)': Syntax error (missing operator) in query expression I am getting this error

What I have tried:

This is what I have Tried and I am getting error in INSERT Query

Dim fd As FileDialog
Dim SelectFile As Variant
Dim cnn As ADODB.Connection
       Set cnn = New ADODB.Connection
       Dim rst As ADODB.Recordset
       Set rst = New ADODB.Recordset
   Set fd = Application.FileDialog(msoFileDialogOpen)
   With fd
       .AllowMultiSelect = False
       .Title = "Please select file to attach"
       If .Show = True Then
           SelectFile = .SelectedItems(1)
       Else
           Exit Sub
       End If
   End With
   Set fd = Nothing

       cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\SaveFile.accdb;Persist Security Info=False;"
       cnn.Open
       Set rst = cnn.Execute("INSERT INTO Table1 (AddFiles) VALUES (" & SelectFile & ")")
      ' rst.AddNew
       rst.Fields(1).Value = "AddFiles"

       rst.Update
       rst.Close
       cnn.Close

解决方案

This is an SQL injection: your filename is promoted to SQL code and the SQL server try to understand the filename as some SQL code.
First step is to change to:

Set rst = cnn.Execute("INSERT INTO Table1 (AddFiles) VALUES ('" & SelectFile & "')")

it will handle filenames without apostrophe. if a filename can have an apostrophe, the problem is the same, real solution you need to read the following articles.
----
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

Try using a CreateParameter Method (ADO) | Microsoft Docs[^] to create a new parameter for your insert statement. Also use Execute Method (ADO Command) | Microsoft Docs[^] to run the INSERT statement.

For a more detailed example, have a look at Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects - Access World Forums[^]

这篇关于我遇到SQL查询问题我试图将文件保存到访问数据库但是运行时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！