通过VBA excel将非英语字符导入SQL的问题 [英] Problem with importing non english character to SQL by VBA excel

问题描述

我可以通过VBA代码将excel中的非英语字符（名为Goods的Sheet3）导入SQL，但之后我的字符更改为问号（？），实际上我将它们丢失了。



如何修复我的VBA代码。



我尝试过的事情：

 私有  Sub  CommandButton1_Click （）
  Dim  con  As  ADODB.Connection 
  Dim  iRowNo  As  整数 
  Dim  sGoodsName，sGoodsCode，sunit  As   String  
 
 设置 con = 新 ADODB.Connection 
 con.Open   driver = {SQL Server};&   Server = DESKTOP-P163904; authenticateuser = True; database = sample1 
 
 iRowNo =  2  
  直到工作表（  货物）。单元格（iRowNo， 1 ）=   
 sGoodsName =工作表（ 商品）。单元格（iRowNo， 1 ）
 sGoodsCode =工作表（ 商品）。单元格（iRowNo， 2 ）
 sunit =工作表（ 商品）。单元格（iRowNo， 3 ）
 
 con.Execute  插入test3（GoodsName，GoodsCode，unit）值（'& sGoodsName&  '，'& sGoodsCode&  '，'&苏尼特&  '） 
 iRowNo = iRowNo +  1  
 循环 
 
 con.Close 
 设置 conn = 没有 
 
 结束  Sub  

解决方案

首先，检查目标列是否定义为 NVARCHAR （ nchar和nvarchar（Transact-SQL）| Microsoft Docs [ ^ ]）。



之后，使用前缀 N 在文字值之前。换句话说：

 con.Execute   insert into test3（GoodsName，GoodsCode，unit）值（N'& sGoodsName&  '，N '& sGoodsCode&  '，N'& sunit&  '） 

作为旁注，为保护数据库免受SQL注入并确保正确插入撇号，如果数据中存在，则应使用参数。查看命令对象参数| Microsoft Docs [ ^ ]

不是您问题的解决方案，而是您遇到的另一个问题。

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

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

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

I can import my non English characters from excel (Sheet3 that named "Goods") to SQL by VBA code but after that my characters changed to Question mark (?) and I lost them actually.

how can I fix my VBA code.

What I have tried:

Private Sub CommandButton1_Click()
   Dim con As ADODB.Connection
   Dim iRowNo As Integer
   Dim sGoodsName, sGoodsCode, sunit As String

   Set con = New ADODB.Connection
   con.Open "driver={SQL Server};" & "Server=DESKTOP-P163904;authenticateuser=True;database=sample1"

   iRowNo = 2
   Do Until Worksheets("Goods").Cells(iRowNo, 1) = ""
      sGoodsName = Worksheets("Goods").Cells(iRowNo, 1)
      sGoodsCode = Worksheets("Goods").Cells(iRowNo, 2)
      sunit = Worksheets("Goods").Cells(iRowNo, 3)

      con.Execute "insert into test3 (GoodsName, GoodsCode, unit) values ('" & sGoodsName & "', '" & sGoodsCode & "', '" & sunit & "')"
      iRowNo = iRowNo + 1
   Loop

   con.Close
   Set conn = Nothing

End Sub

解决方案

First of all, check that the target column is defines as NVARCHAR (nchar and nvarchar (Transact-SQL) | Microsoft Docs[^]).

After that, use prefix N before the literal values. In other words:

con.Execute "insert into test3 (GoodsName, GoodsCode, unit) values (N'" & sGoodsName & "', N'" & sGoodsCode & "', N'" & sunit & "')"

As a side note, to protect the database from SQL injection and to ensure that apostrophe is correctly inserted, if present in the data, you should use parameters. Have a look at Command Object Parameters | Microsoft Docs[^]

Not a solution to your question, but another problem you have.
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[^]

这篇关于通过VBA excel将非英语字符导入SQL的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！