Creating a string with double and single quotes inside for MS SQL query

tcarnes

I am trying to send a query to MSSQL that needs to contain both single and double quotes.

WHERE (SectionXml.exist('//paragraph/text()[contains(.,"search text")]')=1)

In building the query, I escape the inner double quotes as such

$xmlSearch = "WHERE (SectionXml.exist('//paragraph/text()[contains(.,\"$text\")]')=1)";

However, this causes an error in SQL.

I've outputted the content of the query string

Echo $xmlSearch;

and then cut and pasted the resulting query into SQL Server, and the query runs fine; however, it dies when run in the PHP program.

My only guess is that although PHP understands the escaped double quote when echoing to the screen, something else is happening when a string containing an escaped double quote is sent to SQL Server.

Is there any other way to create a string that contains both single and double quotes inside?

djjjozsi

maybe:

$sql=<<<EOF
WHERE (SectionXml.exist('//paragraph/text()[contains(.,"search text")]')=1)
EOF;

tcarnes

Turns out that the problem is not with the double quotes or the single quotes!

I've simplified the query to a simple:

select sectionid from LabelData.dbo.[Section] where sectionid < 41

This works fine, but if I try:

select sectionxml from LabelData.dbo.[Section] where sectionid < 41

where sectionxml is an xml field, it dies with an "unknown error."

Likewise, if I try:

select sectionxmltext from LabelData.dbo.[Section] where sectionid < 41

where sectionxmltext is an nvarchar field, it, too, dies with an "unknown error."

I've loaded the new php_sqlsrv.dll extension recommended by Microsoft, but I'm still getting this error.

So evidently, the problem is with PHP not being able to handle xml or nvarchar field data.

Any suggestions?