Solved

# Encoding Escape Characters In SQL Statements Executed Through PowerShell

Posted on 2008-10-31
1,627 Views
Hello,

I am attempting to execute a an SQL query through PowerShell against an MS Access database.  My query works 99% of the time, but occasionally a localized string will cause an error beacuse it contains a single quote (') somewhere within the localized string.  You can look at the following example to get a better idea of what I am talking about:

SELECT TermNo, File, Context
FROM ExceptionFC
WHERE TermNo = 1
AND File = '\\fr\Content\fr_localized_content.xml'
AND Context = '(le second niveau), lequel doit accéder aux ressources d'un serveur de base de données ou d'un partage réseau (le troisième niveau). Le jeton de sécurité principal utilisé entre le navigateur et le s'

In most languages I could add slashes to this string to escape out the single quotes (') in the string.  But I don's seem to be able to do that in PowerShell.

Is there a way I can execute this query within PowerShell and receive an accurate response back from the database?

Thank you.
test-scripts.zip
0
Question by:bgsullivan
• 3
• 3
• 2

LVL 51

Expert Comment

ID: 22850040
for SQL you need to double the single quote
0

LVL 18

Expert Comment

ID: 22850849
- The escape character in Powershell is backtic ""
You could do a -replace on the string like this
$string -replace "'","'" Probably a better option is to use this script from Lee http://www.leeholmes.com/blog/InteractingWithSQLDatabasesInPowerShellInvokeSqlCommand.aspx 0 Author Comment ID: 22851038 Yeah, i've tried that and I still get errors. For instance, the following SQL: SELECT TermNo, File, Context FROM ExceptionFC WHERE TermNo = 1 AND File = '\\fr\Content\fr_localized_content.xml' AND Context = '(le second niveau), lequel doit accéder aux ressources d'un serveur de base de données ou d'un partage réseau (le troisième niveau). Le jeton de sécurité principal utilisé entre le navigateur et le s' Will return the following error: Exception calling "Fill" with "1" argument(s): "Syntax error (missing operator) in query expression 'TermNo =2 AND File = '\\it\itlocalization.xls' AND Context = '(le second niveau), lequal doit acceder aux ressources d'un serve'." At C:\Temp\test_script_failing.ps1:29 char 22 + [void]$adapter.fill(<<<< $dataset) When I remove the single quotes from the 'Context' string it works fine. Any further ideas? 0 LVL 18 Expert Comment ID: 22851105 Where is the code that creates$dataset?

0

Author Comment

ID: 22851190
I included test scripts that will replicate the error in the origional post.  If you download the ZIP file you will find a working example and a failing example.

Thanks.
0

LVL 51

Accepted Solution

ahoffmann earned 225 total points
ID: 22851194
> .. "Syntax error (missing operator) in query expression
sounds like an error from yur db
As I already said: you need to double single quotes in your text *before* you pass it to the database.
0

LVL 18

Assisted Solution

BSonPosh earned 225 total points
ID: 22851296
I agree with ahoffmann that the error is being returned by SQL.

I would replace the inner single quotes "'"  with double quotes "
-replace "'","""

or as ahoffmann suggest... double the internal single quotes
-replace "'","''"
0

Author Comment

ID: 22874417
Sorry for the delay in response but I was in meetings all day yesterday.  The single escape sequence solution does not work but the dual escape sequence does work.  Thank you both for your help.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Title # Comments Views Activity
exchange, scripts 30 78
How to revise an Access 2003 query into a SQL Server 2008 SQL statement? 1 24
Simple Q for someone 3 17
Running Total in Access 4 13
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Set OWA language and time zone in Exchange for individuals, all users or per database.
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

#### 920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!