Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Encoding Escape Characters In SQL Statements Executed Through PowerShell

Posted on 2008-10-31
8
Medium Priority
?
1,885 Views
Last Modified: 2012-05-05
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
Comment
Question by:bgsullivan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 51

Expert Comment

by:ahoffmann
ID: 22850040
for SQL you need to double the single quote
0
 
LVL 18

Expert Comment

by:BSonPosh
ID: 22850849
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
 

Author Comment

by:bgsullivan
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Expert Comment

by:BSonPosh
ID: 22851105
Where is the code that creates $dataset?

0
 

Author Comment

by:bgsullivan
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

by:
ahoffmann earned 900 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

by:BSonPosh
BSonPosh earned 900 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

by:bgsullivan
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

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

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

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

Join & Ask a Question