View Issue Details

Related ChangesetsJump to NotesJump to History
This issue affects 1 person(s).
 10
IDProjectCategoryView StatusDate SubmittedLast Update
19799Bug reportsSurvey editingpublic2024-10-10 12:312024-10-28 17:41
Reporter2BITS_PL Assigned Totibor.pacalat  
PrioritynoneSeverityblock 
Status closedResolutionfixed 
Product Version6.6.x 
Summary19799: No access to the panelintegration page - returns a 500 error (related to MSSQL).
Description

A few days ago, I set up a clean instance of the LS project in version 6.6.3.
When accessing the page: "surveyAdministration/rendersidemenulink&subaction=panelintegration&surveyid=246225

It returns an error:
CDbCommand failed to execute the SQL statement: SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error. The SQL statement executed was: SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE (((t.sid=:surveyid) AND (questionl10ns.language=:language OR questionl10ns.language IS NULL)) AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)). Bound with :surveyid='246225', :language='pl'

ls-new-v663\vendor\yiisoft\yii\framework\db\CDbCommand.php(543)
543: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

Steps To Reproduce

The problem somewhat relates to how parameters are bound in a complex SQL query. I can't pinpoint the exact cause of the issue. I conducted tests with different configurations, and each parameter binding method results in failure.

Attached, I am providing the results of the tests:

  • Test 1 and 2: These are just basic verifications.

  • Test 3: Here is a simulation of the original SQL query that generates the error. Invoked as follows:
    $command = Yii::app()->db->createCommand($sql);
    $result = $command->queryScalar($params);

  • Test 4: This uses bindValue as follows:
    $command = Yii::app()->db->createCommand($sql);
    $command->bindValue(":surveyid", (int) $params[':surveyid']);
    $command->bindValue(":language", $params[':language']);
    $result = $command->queryScalar();

  • Test 5: This is the same as test 3 but executed using PDO:
    $pdo = new PDO('sqlsrv:Server=localhost;Database=ls-new-v663-20240918', 'username', 'pass');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

The remaining tests are based on the traditional method, which refers to "test 3" but modifies the original SQL, and interestingly, all pass successfully.

  • Test 7: This checks whether it works well when the values are passed directly in SQL (without binding).
    $result = $command->queryScalar();

  • Test 8: This checks whether it works when only the 'parameter is passed while 'language' is specified in the SQL query
    $result = $command->queryScalar($params);

  • Test 9: This checks passing both parameters, but in such a way that only one 'language' is set as a parameter while the other is hardcoded in the SQL.

  • Test 10: This checks passing both parameters but removes "subquestionl10ns.language='pl'" from the SQL.

Conclusions: The problem is that I don’t understand why parameter binding works when appears once in the SQL query, but when it appears twice, it doesn’t work.

TagsNo tags attached.
Attached Files
result_test_mssql_query.html (9,504 bytes)    
<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head><body><h3>1. Test Simple - <b style="color:green">POSITIVE</b></h3>SQL: <br><code>
	SELECT COUNT(DISTINCT [t].[id]) 
	FROM [dbo].[lime_survey_url_parameters] [t] 
	WHERE t.sid=:surveyid
</code><br><br>Params: <br>array(1) {
  [":surveyid"]=&gt;
  int(246225)
}
<br><br><strong style="color:blue">Result: <br>0</strong><hr><h3>2. Test COUNT(DISTINCT)) - <b style="color:green">POSITIVE</b></h3>SQL: <br><code>SELECT COUNT(DISTINCT [id]) 
                 FROM [dbo].[lime_survey_url_parameters] 
                 WHERE sid = :surveyid</code><br><br>Params: <br>array(1) {
  [":surveyid"]=&gt;
  int(246225)
}
<br><br><strong style="color:blue">Result: <br>0</strong><hr><h3>3. Test original query (generating error) - traditional bind params - <b style="color:red">NEGATIVE</b></h3>SQL: <br><code>
	SELECT COUNT(DISTINCT [t].[id]) 
	FROM [dbo].[lime_survey_url_parameters] [t] 
	LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) 
	WHERE t.sid=:surveyid
		AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) 
		AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
</code><br><br>Params: <br>array(2) {
  [":surveyid"]=&gt;
  int(246225)
  [":language"]=&gt;
  string(2) "pl"
}
<br><br><strong style="color:red;">Error: <br></strong><i>CDbCommand nie zdołał wykonać instrukcji SQL: SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error. The SQL statement executed was: 
	SELECT COUNT(DISTINCT [t].[id]) 
	FROM [dbo].[lime_survey_url_parameters] [t] 
	LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) 
	WHERE t.sid=:surveyid
		AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) 
		AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
. Bound with :surveyid=246225, :language='pl'</i><hr><h3>4. Test original query - method use bindVaule - <b style="color:red">NEGATIVE</b></h3>SQL: <br><code>
	SELECT COUNT(DISTINCT [t].[id]) 
	FROM [dbo].[lime_survey_url_parameters] [t] 
	LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) 
	WHERE t.sid=:surveyid
		AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) 
		AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
</code><br><br>Params: <br>array(2) {
  [":surveyid"]=&gt;
  int(246225)
  [":language"]=&gt;
  string(2) "pl"
}
<br><br><strong style="color:red;">Error: <br></strong><i>CDbCommand nie zdołał wykonać instrukcji SQL: SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error. The SQL statement executed was: 
	SELECT COUNT(DISTINCT [t].[id]) 
	FROM [dbo].[lime_survey_url_parameters] [t] 
	LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) 
	WHERE t.sid=:surveyid
		AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) 
		AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
. Bound with :surveyid=246225, :language='pl'</i><hr><h3>5. Test original query - method use PDO - <b style="color:red">NEGATIVE</b></h3>SQL: <br><code>
	SELECT COUNT(DISTINCT [t].[id]) 
	FROM [dbo].[lime_survey_url_parameters] [t] 
	LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) 
	LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) 
	WHERE t.sid=:surveyid
		AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) 
		AND (subquestionl10ns.language=:language OR subquestionl10ns.language IS NULL)
</code><br><br>Params: <br>array(2) {
  [":surveyid"]=&gt;
  int(246225)
  [":language"]=&gt;
  string(2) "pl"
}
<br><br><strong style="color:red;">Error: <br></strong>SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error<br><br><hr><hr><h3>7. Test original query - without all parameters - <b style="color:green">POSITIVE</b></h3>SQL: <br><code>SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE <strong style="color:red;">t.sid=246225</strong> AND <strong style="color:red;">(questionl10ns.language='pl'</strong> OR questionl10ns.language IS NULL) AND <strong style="color:red;">(subquestionl10ns.language='pl'</strong> OR subquestionl10ns.language IS NULL)</code><br><br>Params: <br>array(0) {
}
<br><br><strong style="color:blue">Result: <br>0</strong><hr><h3>8. Test original query - without language parameter - <b style="color:green">POSITIVE</b></h3>SQL: <br><code>SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE t.sid=:surveyid AND <strong style="color:red;">(questionl10ns.language='pl'</strong> OR questionl10ns.language IS NULL) AND <strong style="color:red;">(subquestionl10ns.language='pl'</strong> OR subquestionl10ns.language IS NULL)</code><br><br>Params: <br>array(1) {
  [":surveyid"]=&gt;
  int(246225)
}
<br><br><strong style="color:blue">Result: <br>0</strong><hr><h3>9. Test original query - without one language parameter - <b style="color:green">POSITIVE</b></h3>SQL: <br><code>SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE t.sid=:surveyid AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) AND <strong style="color:red;">(subquestionl10ns.language='pl'</strong> OR subquestionl10ns.language IS NULL)</code><br><br>Params: <br>array(2) {
  [":surveyid"]=&gt;
  int(246225)
  [":language"]=&gt;
  string(2) "pl"
}
<br><br><strong style="color:blue">Result: <br>0</strong><hr><h3>10. Test original query - remove "subquestionl10ns.language" - <b style="color:green">POSITIVE</b></h3>SQL: <br><code>SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[lime_survey_url_parameters] [t] LEFT OUTER JOIN [dbo].[lime_questions] [question] ON ([t].[targetqid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [questionl10ns] ON ([questionl10ns].[qid]=[question].[qid]) LEFT OUTER JOIN [dbo].[lime_questions] [subquestion] ON ([t].[targetsqid]=[subquestion].[qid]) LEFT OUTER JOIN [dbo].[lime_question_l10ns] [subquestionl10ns] ON ([subquestionl10ns].[qid]=[subquestion].[qid]) WHERE t.sid=:surveyid AND (questionl10ns.language=:language OR questionl10ns.language IS NULL) AND <strong style="color:red;">(subquestionl10ns.language</strong> <strong style="color:red;">IS</strong> <strong style="color:red;">NULL)</strong> <strong style="color:red;"></strong> <strong style="color:red;"></strong></code><br><br>Params: <br>array(2) {
  [":surveyid"]=&gt;
  int(246225)
  [":language"]=&gt;
  string(2) "pl"
}
<br><br><strong style="color:blue">Result: <br>0</strong><hr></body></html>
result_test_mssql_query.html (9,504 bytes)   
Bug heat10
Complete LimeSurvey version number (& build)6.6.3
I will donate to the project if issue is resolvedNo
Browser
Database type & versionSQL Server 2019
Server OS (if known)
Webserver software & version (if known)
PHP Versionv8.1.29 NTS x64

Users monitoring this issue

There are no users monitoring this issue.

Activities

2BITS_PL

2BITS_PL

2024-10-10 12:36

reporter   ~81214

The issue is that when the parameter ":languange" appears once in SQL, parameter binding works correctly; however, when it appears twice, it fails. This is clearly demonstrated by the results of tests 7 to 10. I don't understand why this problem only occurs with the MSSQL database.
gabrieljenik

gabrieljenik

2024-10-18 19:14

manager   ~81269

https://github.com/LimeSurvey/LimeSurvey/pull/3997

Got to the same conclusion as the described earlier ticket but no idea why...
not sure if it's a SQL Server or driver limitation
DenisChenu

DenisChenu

2024-10-22 08:44

developer   ~81279

SQL Server 2019 didn't like same variable multiple time.
guest

guest

2024-10-22 15:04

viewer   ~81280

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=36595
LimeBot

LimeBot

2024-10-28 17:41

administrator   ~81300

Fixed in Release 6.6.7+241028

Related Changesets

LimeSurvey: master 451e3de6

2024-10-22 17:04

Gabriel Jenik

Committer: GitHub


Details Diff		 Fixed issue 19799: No access to the panelintegration page - returns a 500 error (related to MSSQL) (03997)

Co-authored-by: lapiudevgit <devgit@lapiu.biz>		 Affected Issues
19799
mod - application/models/SurveyURLParameter.php Diff File

Issue History

Date Modified Username Field Change
2024-10-10 12:31 2BITS_PL New Issue
2024-10-10 12:31 2BITS_PL File Added: result_test_mssql_query.html
2024-10-10 12:36 2BITS_PL Note Added: 81214
2024-10-10 12:36 2BITS_PL Bug heat 0 => 2
2024-10-16 17:13 tibor.pacalat Assigned To => gabrieljenik
2024-10-16 17:13 tibor.pacalat Status new => assigned
2024-10-18 19:14 gabrieljenik Assigned To gabrieljenik => DenisChenu
2024-10-18 19:14 gabrieljenik Status assigned => ready for code review
2024-10-18 19:14 gabrieljenik Note Added: 81269
2024-10-18 19:14 gabrieljenik Bug heat 2 => 4
2024-10-22 08:44 DenisChenu Note Added: 81279
2024-10-22 08:44 DenisChenu Bug heat 4 => 6
2024-10-22 08:44 DenisChenu Assigned To DenisChenu => tibor.pacalat
2024-10-22 08:44 DenisChenu Status ready for code review => ready for testing
2024-10-22 15:04 Changeset attached => LimeSurvey master 451e3de6
2024-10-22 15:04 guest Note Added: 81280
2024-10-22 15:04 guest Bug heat 6 => 8
2024-10-22 15:04 tibor.pacalat Status ready for testing => resolved
2024-10-22 15:04 tibor.pacalat Resolution open => fixed
2024-10-28 17:41 LimeBot Note Added: 81300
2024-10-28 17:41 LimeBot Status resolved => closed
2024-10-28 17:41 LimeBot Bug heat 8 => 10