How to Deal with Single Quote in Word Vba SQL Query

How to deal with single quote in Word VBA SQL query?

Where you specify two single quotes '', one will escape the other and will result in single, try to replace it like this:

customerName = Replace(customerName, "'", "''")

Remove front and back white space and insert single quote with comma

Try doing the following find and replace, in regex mode:

Find:    [ ]*(\S+)[ ]*
Replace: '$1',

Demo

Sample script:

Dim RegEx As Object
Dim Cell As Range
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True
RegEx.Pattern = "[ ]*(\S+)[ ]*"

For Each Cell In Selection.Cells
    Cell.Value = RegEx.Replace(Cell.Value, "'$1',")
Next

VBA to Write SQL grabbing table info

When SQL Server encounters two successive single-quotes, it only sees one single-quote. So you would double-up on the single quotes, like this:

WHERE z.brand = '" & REPLACE(strBrand,"'","''") & "' 

single quotes escape during string insertion into a database

try

string sql= "insert into gtable (1text, 1memo) " + 
            "values ('" + textBox3.Text.Replace("'", "''") + "', null)";

How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?

There are a few cases where this escape function will fail. The most obvious is when a single quote isn't used:

string table= "\"" + table.Replace("'", "''") + "\""
string var= "`" + var.Replace("'", "''") + "`"
string index= " " + index.Replace("'", "''") + " "
string query = "select * from `"+table+"` where name=\""+var+"\" or id="+index

In this case, you can "break out" using a double-quote, a back-tick. In the last case there is nothing to "break out" of, so you can just write 1 union select password from users-- or whatever sql payload the attacker desires.

The next condition where this escape function will fail is if a sub-string is taken after the string is escaped (and yes I have found vulnerabilities like this in the wild):

string userPassword= userPassword.Replace("'", "''")
string userName= userInput.Replace("'", "''")
userName = substr(userName,0,10)
string query = "select * from users where name='"+userName+"' and password='"+userPassword+"'";

In this case a username of abcdefgji' will be turned into abcdefgji'' by the escape function and then turned back into abcdefgji' by taking the sub-string. This can be exploited by setting the password value to any sql statement, in this case or 1=1-- would be interpreted as sql and the username would be interpreted as abcdefgji'' and password=. The resulting query is as follows:

select * from users where name='abcdefgji'' and password=' or 1=1-- 

T-SQL and other advanced sql injection techniques where already mentioned. Advanced SQL Injection In SQL Server Applications is a great paper and you should read it if you haven't already.

The final issue is unicode attacks. This class of vulnerabilities arises because the escape function is not aware of multi-byte encoding, and this can be used by an attacker to "consume" the escape character. Prepending an "N" to the string will not help, as this doesn't affect the value of multi-byte chars later in the string. However, this type of attack is very uncommon because the database must be configured to accept GBK unicode strings (and I'm not sure that MS-SQL can do this).

Second-Order code injection is still possible, this attack pattern is created by trusting attacker-controlled data sources. Escaping is used to represent control characters as their character literal. If the developer forgets to escape a value obtained from a select and then uses this value in another query then bam the attacker will have a character literal single quote at their disposal.

Test everything, trust nothing.

Using a Dlookup in Access VBA when the field has a single quote

Though the suggestions and answers here do work and resolve many issues with quotes in text, my issue ended up being related to the character I was seeing as a single quote not really being a single quote. For what it's worth, the data I was using was exported from Siebel, and the single quote I was seeing was actually chr(146), where a regular single quote (I say "regular" for lack of a better term) is chr(39).

If having issues with quotes, I found it helpful to examine the chr values of each character in the string. There may be a better way to do this, but this loop should help:

for i=1 to len(a_string)
    debug.print mid(a_string,i,1) & " - " & asc(mid(a_string,i,1)
next i

The asc function gives you the chr code for a character, so this loops through the string and shows you each character and its associated chr code in the Immediate window (using debug.print). This also helps in finding other "hidden" (or non-visible) characters that may exist in a string.

Once discovered, I used the replace function to replace chr(146) with two single quotes (two chr(39)s), as suggested by HansUp, and that worked perfectly.

SQL to Query text in access with an apostrophe in it

You escape ' by doubling it, so:

Select * from tblStudents where name like 'Daniel O''Neal' 

Note that if you're accepting "Daniel O'Neal" from user input, the broken quotation is a serious security issue. You should always sanitize the string or use parametrized queries.

---

Related Topics