Escaping quotes to build SQL query

[pblancher]

I am using XSL to translate an XML document from a newswire into an SQL insert statment so that the feed can be loaded into a MySQL database.

I have the query actually building without issue, but the problem is that the quotes are messing up when the query fires.

My XSL file is as such:

<xsl:template match="IndexStoryHead">
INSERT INTO newsfeed VALUES (NULL,'<xsl:value-of select="(.)"/>',
</xsl:template>
<xsl:template match="Story">
'<xsl:value-of select="(.)"/>',0);
</xsl:template>

What is outputted is:
INSERT INTO newsfeed VALUES (NULL,'random news title', ' TORONTO - snippet description ',0);

What I need to happen is for the single and double quotes get escaped out in the query. How would I go about doing this?

TIA,

Phil

[edited by: jatar_k at 5:59 pm (utc) on June 19, 2006]
[edit reason] no specifics thanks [/edit]

[coopster]

Hi pblancher and welcome to WebmasterWorld.

What server-side language are you using to push the data into the database? MySQL has an API for escaping the data and some server-side languages such as PHP have integrated functions that perform the task for you.

[pblancher]

I am building the query string from XML, then pushing into Mysql with PHP.

[coopster]

Then perhaps mysql_real_escape_string() [php.net] is the answer to your issue.

[pblancher]

Tried that. The value of $result is the set of queries that were generated by the XSL file. Problem is when you use mysql_real_escape_string() on $result, it also escapes out the single quotes used in the query itself. You get:

INSERT INTO news VALUES (\'item\',\'bob\'s wife left him for you\'re friend\',0);

Instead of:

INSERT INTO news VALUES ('item','bob\'s wife left him for you\'re friend',0);