Codesnip, currently in used in my project: /// summary /// Encode theuser-input (with special character) into SQL query statement /// special character like : ',[,/,_,%...etc /// /summary /// param name="strValue" user-input /param /// par
Codesnip, currently in used in my project:
///
/// Encode theuser-input (with special character) into SQL query statement
/// special character like : ',[,/,_,%...etc
///
/// user-input
/// if it is encode for like statement
/// SQL query statement
publicstaticstring sqlEncode( string strValue, bool isLikeStatement)
{
string rtStr = strValue;
if (isLikeStatement)
{
rtStr = strValue.Replace( "[" , "[[]" ); // 此句一定要在最前
rtStr = rtStr.Replace( "_" , "[_]" );
rtStr = rtStr.Replace( "%" , "[%]" );
rtStr = rtStr.Replace( @"/" , "////" );
}
rtStr = rtStr.Replace( "'" , "''" );
return rtStr;
}
===============================ppll的分割线==================================
查询SqlServer特殊字符 原文：Here
我们都知道 SQL 查询过程中，单引号[ ' ]是特殊字符，所以在查询的时候要转换成双单引号[ '' ]。
但这只是特殊字符的一个，在实际项目中，发现对于 like 操作还有以下特殊字符：下划线[ _ ]，百分号[ % ]，方括号[ [] ]以及尖号[ ^ ]。
其用途如下：
下划线：用于代替一个任意字符（相当于正则表达式中的 ? ）
百分号：用于代替任意数目的任意字符（相当于正则表达式中的 * ）
方括号：用于转义（事实上只有左方括号用于转义，右方括号使用最近优先原则匹配最近的左方括号）
尖号：用于排除一些字符进行匹配（这个与正则表达式中的一样）
以下是一些匹配的举例，需要说明的是，只有 like 操作才有这些特殊字符， = 操作是没有的。
a_b...
a [_] b%
a%b...
a [%] b%
a[b...
a [[] b%
a]b...
a]b%
a[]b...
a [[] ]b%
a[^]b...
a [[] [^] ]b%
a[^^]b...
a [[] [^] [^] ]b%
对于 like 操作，需要进行以下替换（注意顺序也很重要）
[ -> [[] ( 这个必须是第一个替换的 !!)
% -> [%] ( 这里 % 是指希望匹配的字符本身包括的 % 而不是专门用于匹配的通配符 )
_ -> [_]
^ -> [^]
===============================ppll的分割线==================================
引用：Here
SQL encode and decode Function
Function SQL_encode(strContent)
If isnull(strContent) = False Then
strContent = replace(strContent, """", """)
strContent = replace(strContent, "'", "'")
strContent = replace(strContent, "+", "+")
strContent = replace(strContent, "*", "*")
strContent = replace(strContent, "-", "-")
strContent = replace(strContent, "=", "=")
strContent = replace(strContent, " strContent = replace(strContent, ">", ">")
strContent = replace(strContent, "%", "%")
strContent = replace(strContent, "_", "_")
SQL_encode = strContent
End If
End Function
Function SQL_decode(strContent)
If isnull(strContent) = False Then
strContent = replace(strContent, """, """")
strContent = replace(strContent, "'", "'")
strContent = replace(strContent, "+", "+")
strContent = replace(strContent, "*", "*")
strContent = replace(strContent, "-", "-")
strContent = replace(strContent, "=", "=")
strContent = replace(strContent, "<", " strContent = replace(strContent, ">", ">")
strContent = replace(strContent, "%", "%")
strContent = replace(strContent, "_", "_")
SQL_Decode = strContent
End If
End Function
-------------------------------------------------------------------
'transform any SQL operators to their ascii equivalent
function SQL_encode(strContent)
if isnull(strContent) = false then
'transform sql operators to ascii equivalents
strContent = replace(strContent, "'", "|Q|")
strContent = replace(strContent, """", "|QQ|")
strContent = replace(strContent, "+", "|PLUS|")
strContent = replace(strContent, "*", "|STAR|")
strContent = replace(strContent, "-", "|MINUS|")
strContent = replace(strContent, "=", "|EQUALS|")
strContent = replace(strContent, " strContent = replace(strContent, ">", "|RIGHT|")
strContent = replace(strContent, "%", "|PERCENT|")
strContent = replace(strContent, "_", "|UNDER|")
strContent = replace(strContent, "/", "|BACKS|")
strContent = replace(strContent, "/", "|FRONTS|")
SQL_encode = strContent
end if
end function
'tranform ascii characters to their SQL equivalent
function SQL_decode(strContent)
if isnull(strContent) = false then
'transform sql operators
strContent = replace(strContent, "|Q|", "'")
strContent = replace(strContent, "|QQ|", """")
strContent = replace(strContent, "|PLUS|", "+")
strContent = replace(strContent, "|STAR|", "*")
strContent = replace(strContent, "|MINUS|", "-")
strContent = replace(strContent, "|EQUALS|", "=")
strContent = replace(strContent, "|LEFT|", " strContent = replace(strContent, "|RIGHT|", ">")
strContent = replace(strContent, "|PERCENT|", "%")
strContent = replace(strContent, "|UNDER|", "_")
strContent = replace(strContent, "|BACKS|", "/")
strContent = replace(strContent, "|FRONTS|", "/")
SQL_Decode = strContent
end if
end function
查看更多关于SqlServer特殊字符转换&amp;查询的详细内容...