Encoding Escape Characters In SQL Statements Executed Through PowerShell

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
bgsullivanAsked:
Who is Participating?
 
ahoffmannConnect With a Mentor Commented:
> .. "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
 
ahoffmannCommented:
for SQL you need to double the single quote
0
 
BSonPoshCommented:
To answer your question directly
- 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
Will You Be GDPR Compliant by 5/28/2018?

GDPR? That's a regulation for the European Union. But, if you collect data from customers or employees within the EU, then you need to know about GDPR and make sure your organization is compliant by May 2018. Check out our preparation checklist to make sure you're on track today!

 
bgsullivanAuthor Commented:
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
 
BSonPoshCommented:
Where is the code that creates $dataset?

0
 
bgsullivanAuthor Commented:
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
 
BSonPoshConnect With a Mentor Commented:
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
 
bgsullivanAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.