Single/Double quote issues with raw SQL

[Arvind Satyanarayan]

Hi All,

Apologies if this gets posted twice... I've been running into some strange issues when trying to execute raw SQL queries via an adapter. For example, with this query:

DataMapper.repository(:default).adapter.execute('insert into dom_nodes (page_id, tag_name, parent_id, dom_id) values (1, "DIV", 2, 3)')

I get the following error (although the query is valid and runs successfully via the MySQL console). 

Unknown column 'DIV' in 'field list' (code: 1054, sql state: 42S22, query: insert into dom_nodes (page_id, tag_name, parent_id, dom_id) values (1, "DIV", 2, 3)

Changing the double quotes around DIV to single quotes cause it to execute successfully via DataMapper. However, for more complex queries where I escape single quotes, I get an "SQL syntax" error (but again, the query that's part of the error message is valid and runs successfully in the MySQL console). Here's an example of such a query: http://pastiebin.com/?page=p&id=4fc2c7e7e8ecc

Any ideas on what could be causing this? Are there things DataMapper is doing in the background for adapter query executions?

Thanks!

-Arvind

[Chris Corbyn]

Double quotes have a special meaning in SQL.  They are used for quoting column and table names.  MySQL usually runs in a non-compliant mode, which allows you to write SQL that assumes differing conventions.  DataMapper runs in a strict mode that forces MySQL to be (more) compliant with the standards.

Just use single quoted strings if you're hard-coding the whole query.

However, what you should really be doing is using bind parameters, so that they can be transported to the DBMS safely.

adapter.execute("INSERT INTO dom_nodes (page_id, tag_name, parent_id, dom_id) VALUES (?, ?, ?, ?)", 1, "DIV", 2, 3)

--
You received this message because you are subscribed to the Google Groups "DataMapper" group.
To view this discussion on the web visit https://groups.google.com/d/msg/datamapper/-/zrtVU10H73UJ.
To post to this group, send email to datam...@googlegroups.com.
To unsubscribe from this group, send email to datamapper+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.

[Arvind Satyanarayan]

Thanks for the reply Chris. I tried single quoted strings but experienced SQL syntax errors when the strings themselves contained escaped single quotes... I'm guessing this might be related to the fact that DM runs in strict mode? 

Thanks for the parameter binding syntax, that worked like a charm. 

-Arvind

To unsubscribe from this group, send email to datamapper+unsubscribe@googlegroups.com.