如何在动态SQL子句中转义单引号？ [英] how to escape single quotes in a dynamic SQL clause?

问题描述

您好，



我的应用程序中有一个插入查询（vb.net 3.5; Oracle 10g）



Dim queryString As String =插入元数据（OBJECT_NAME，TITLE，ROW_NUM1）值（'+ strObjName +'，'+ strTitle +'，myDB.SEQ_ID.NEXTVAL）



标题栏中有时会有含撇号的单词。在尝试插入这些值时，我收到错误 -



ORA-01756引用字符串未正确终止



谁能告诉我如何逃避这个？



谢谢！

Hello,

I have an insert query in my application (vb.net 3.5; Oracle 10g)

Dim queryString As String = "insert into metadata (OBJECT_NAME, TITLE, ROW_NUM1) values ('" + strObjName + "', '" + strTitle + "', myDB.SEQ_ID.NEXTVAL)"

The title column at times have words having a apostrophe. While trying to insert these values am getting the error -

ORA-01756 quoted string not properly terminated

Can anyone tell me how to escape this?

Thank you!

推荐答案

你可以通过参数化你的sql查询来避免这种麻烦，参考： VB.NET：将带有单引号的文本转换为上传到Oralce DB [ ^ ]

You can avoid such a hassle by parameterize your sql query, refer: VB.NET: convert text with single quote to upload to Oralce DB[^]

你可以通过用两个单引号替换单引号来逃避它：''。



但是，我建议使用参数化查询而不是转义字符串。为什么？因为如果您使用字符串连接（就像您现在这样）来创建命令，则不会受到 SQL注入 [< a href =http://en.wikipedia.org/wiki/SQL_injectiontarget =_ blanktitle =New Window> ^ ]。您可以使用如下命名参数：

You can escape it by replacing the single quote by two single quotes: ''.

However, I suggest to use a parameterized query instead of escaping the string. Why? Because if you use string concatenation (like you do now) to create your command, you are not protected against SQL Injection[^]. You can use named parameters like this:

Dim queryString As String = "insert into metadata (OBJECT_NAME, TITLE, ROW_NUM1) values (:objName, :title, myDB.SEQ_ID.NEXTVAL)"
Dim command As OracleCommand = New OracleCommand(query, connection) ' replace connection by the name of the variable of your OracleConnection
command.CommandType = CommandType.Text
command.Parameters.Add(":objName", OracleDbType.Varchar2).Value = strObjName
command.Parameters.Add(":title", OracleDbType.Varchar2).Value = strTitle

这篇关于如何在动态SQL子句中转义单引号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！