插入访问数据库时转义单引号 [英] Escape single quote when inserting into an access database

问题描述

我有一个应用程序，它通过ODBC从Excel spreadsheeet读取作业候选数据，并通过ODBC在ACCESS表中填充它。

我现在面临的问题是一些文本字段在Excel中包含单引号。

如何动态地将这些数据插入Access？



谢谢



我的尝试：



Visual Studio在线帮助。

解决方案

< blockquote>使用参数化查询，或通过DataTable。

唯一一次出现问题的时候是连接字符串，这在任何时候都是个坏主意。

永远不会连接用于构建SQL命令的字符串。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  Baker' s Wood ' < span class =code-string>  

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x';  DROP  表 MyTable;   -   ' 

哪个SQL看作三个单独的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x'; 

完全有效的SELECT

  DROP   TABLE  MyTable; 

完全有效的删除表格通讯和

   -   ' 

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期进行备份，不是吗？

请参考：在Access数据库中使用特殊字符时出现错误消息 [ ^ ]

MSDN写道：

解决方法

要解决此问题，请不要使用特殊字符。如果必须在查询表达式中使用特殊字符，请将特殊字符括在括号中（ [] ）。例如，如果您要使用大于号（> ），请使用 [>] 。

BTW：我完全赞同OriginalGriff，特别是关于使用参数化查询 [ ^ ]。

将'替换为''。



这样的C ++代码替换很容易写：

  void  escapeTicks（std： ：wstring * s）
 {
  for （ size_t  i =  0 ;（* s）[i]！=  0 ;）
 {
  if （（* s）[i] == L '  \\ \\''）
 {
（* s）.insert（i，L  '）; 
 i + =  2 ; 
} 
  else  
 {
 i ++; 
} 
} 
} 

我还没有测试过代码，但它应该工作

实际上，旧代码不起作用。感谢匿名评论者（评论中没有出现这个名字）我有一个很好的代码。



来源：Escaping - 堆栈溢出 [ ^ ]

I have an application which reads job candidates data from an Excel spreadsheeet via ODBC and populates the same in an ACCESS table via ODBC.
The problem I am facing now is that some of the text fields in Excel contain single quotes.
How can I insert such data into Access dynamically?

Thanks

What I have tried:

Visual Studio deugging Online help.

解决方案

Using parameterised queries, or via a DataTable.
The only time it gives problems is when you concatenate strings, and that's a bad idea at any time.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Please, refer this: Error message when you use special characters in Access databases[^]

MSDN wrote:

Workaround

To work arond this problem, do not use special characters. If you must use special characters in query expressions, enclose the special characters in brackets ([]). For example, if you want to use the greater than sign (>), use [>].

BTW: i completely agree with OriginalGriff, especially about using parameterized queries[^].

Replace "'" with "''".

The C++ code for such a replacement is very easy to write:

void escapeTicks(std::wstring *s)
{
    for (size_t i = 0;(*s)[i] != 0;)
    {
        if ((*s)[i] == L'\'')
        {
            (*s).insert(i, L"'");
            i += 2;
        }
        else
        {
            i++;
        }
    }
}

I have not tested the code, but it should work
Actually, the old code did not work. Thanks to an anonymous commenter (the name does not appear in the comment) I have a good code.

Source: Escaping ' in Access SQL - Stack Overflow[^]

这篇关于插入访问数据库时转义单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！