PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'END'.: SELECT TOP(1) ncs.last_comment_timestamp AS last_comment_timestamp, ncs.last_comment_uid AS last_comment_uid, CASE ncs.last_comment_uid WHEN 0 THEN ncs.last_comment_name ELSE u2.name END AS last_comment_name FROM {node} n INNER JOIN {users} u1 ON n.uid = u1.uid INNER JOIN {forum} f ON n.vid = f.vid AND f.tid = :tid INNER JOIN {node_comment_statistics} ncs ON n.nid = ncs.nid INNER JOIN {users} u2 ON ncs.last_comment_uid = u2.uid WHERE ( (n.status = :db_condition_placeholder_0) ) ORDER BY last_comment_timestamp DESC; Array ( [:db_condition_placeholder_0] => 1 [:tid] => 1 ) in forum_forum_load() (line 827 of D:\www\d7\modules\forum\forum.module).

I haven't looked at the line it refers to yet. For now I'm surveying the landscape and noting such issues. Will look at the code later.

Note forum/1 works fine. This is with a fresh install which I completed last Friday.

CommentFileSizeAuthor
#4 858776-improve-protected-words.patch2.4 KBdamien tournoud
#4 sqlsrv.test4.2 KBdamien tournoud

Comments

damien tournoud’s picture

damien tournoud commented

We recently changed this query to be more portable. This was previously using the IF() function that we implemented as a UDF, and is now using the ANSI SQL CASE / WHEN / ELSE / END.

I'm confused about the error message, because the syntax really looks correct:

CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

(from http://msdn.microsoft.com/en-us/library/ms181765.aspx)

damien tournoud’s picture

damien tournoud commented
Title: forum » Remove or improve the protection against reserved words
Priority: Normal » Critical

Ok, this is due to the regular expression that tries to quote reserved words. We need to shoot it down, because quoting reserved words using a regular expression cannot really work.

One easy thing we could do is simply reduce the list of reserved words to the ones that are reserved words in SQL Server and not on MySQL, PostgreSQL and SQLite. END is typically reserved in all of those, so it should not be quoted anyway.

damien tournoud’s picture

damien tournoud commented
Priority: Critical » Major

I removed END from the list of reserved words, making this issue only Major.

damien tournoud’s picture

damien tournoud commented
Status: Active » Fixed
StatusFileSize
new sqlsrv.test4.2 KB
new 858776-improve-protected-words.patch2.4 KB

I committed the following patch, which should solve the issue in most cases. I also have a test case, that cannot really be committed until we have done #982564: Add an helper module.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.