View Issue Details

IDProjectCategoryView StatusLast Update
0012674mantisbtdb mssqlpublic2015-03-15 19:58
Reporterted Assigned Todregad  
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
PlatformIISOSWindowsOS Version2003
Product Version1.2.4 
Target Version1.3.0-beta.2Fixed in Version1.3.0-beta.2 
Summary0012674: APPLICATION ERROR 0000401 on manage_user_edit_page.php and other occasions
Description

This is probably a bug in the ODBC driver, I'm using SQL Native Client 2005.90.1399.00 (according to ODBC Admin) with SQL Server 2005. PHP Version is 5.3.5

The problem occurs in core\print_api.php, function print_project_user_list_option_list2

Steps To Reproduce
  • Create a new user account on the start page
  • Log in as admin and try to edit user properties
Additional Information

I guess the problem is that this ODBC driver cannot handle bound variables in LEFT JOIN ON... statements. After modifying the function to the following everything worked fine:

function print_project_user_list_option_list2( $p_user_id ) {
$t_mantis_project_user_list_table = db_get_table( 'mantis_project_user_list_table' );
$t_mantis_project_table = db_get_table( 'mantis_project_table' );

$c_user_id = db_prepare_int( $p_user_id );

$query = "SELECT DISTINCT p.id, p.name

FROM $t_mantis_project_table p

LEFT JOIN $t_mantis_project_user_list_table u

ON p.id=u.project_id AND u.user_id=" . db_param() . "

WHERE p.enabled = " . db_param() . " AND

u.user_id IS NULL

ORDER BY p.name";

$query = "SELECT DISTINCT p.id, p.name
            FROM $t_mantis_project_table p
            LEFT JOIN $t_mantis_project_user_list_table u
            ON p.id=u.project_id AND u.user_id=$c_user_id
            WHERE p.enabled = " . db_param() . " AND
                u.user_id IS NULL
            ORDER BY p.name";

$result = db_query_bound( $query, Array( $c_user_id, true ) );

$result = db_query_bound( $query, Array( true ) );
$category_count = db_num_rows( $result );
for( $i = 0;$i < $category_count;$i++ ) {
    $row = db_fetch_array( $result );
    $t_project_name = string_attribute( $row['name'] );
    $t_user_id = $row['id'];
    echo "<option value=\"$t_user_id\">$t_project_name</option>";
}

}

The same error occured in other sutuations, like verifying a newly created account, and went away when I replaced bound variables in LEFT JOIN ON statements by directly inserting the variables.

Doing a grep for "ON.*db_param" turned up a total of 6 occurances.

TagsNo tags attached.
Attached Files
sql_server_1.2.4.patch (6,950 bytes)   
From 65ca4c9f7cf5266ad2be15fa939a279e3df5461d Mon Sep 17 00:00:00 2001
From: Bernhard Froehlich <ted@convey.de>
Date: Tue, 11 Jan 2011 23:52:12 +0100
Subject: [PATCH] Removing parameters from JOIN ... ON clauses

---
 core/print_api.php |    8 ++++----
 core/tag_api.php   |    7 ++++---
 core/user_api.php  |    8 ++++----
 summary_page.php   |    8 ++++----
 4 files changed, 16 insertions(+), 15 deletions(-)

diff --git a/core/print_api.php b/core/print_api.php
index eaec5f2..993d2f5 100644
--- a/core/print_api.php
+++ b/core/print_api.php
@@ -1051,12 +1051,12 @@ function print_project_user_list_option_list( $p_project_id = null ) {
 	$query = "SELECT DISTINCT u.id, u.username, u.realname
 				FROM $t_mantis_user_table u
 				LEFT JOIN $t_mantis_project_user_list_table p
-				ON p.user_id=u.id AND p.project_id=" . db_param() . "
+				ON p.user_id=u.id AND p.project_id=$c_project_id
 				WHERE u.access_level<" . db_param() . " AND
 					u.enabled = " . db_param() . " AND
 					p.user_id IS NULL
 				ORDER BY u.realname, u.username";
-	$result = db_query_bound( $query, Array( $c_project_id, $t_adm, true ) );
+	$result = db_query_bound( $query, Array( $t_adm, true ) );
 	$t_display = array();
 	$t_sort = array();
 	$t_users = array();
@@ -1097,11 +1097,11 @@ function print_project_user_list_option_list2( $p_user_id ) {
 	$query = "SELECT DISTINCT p.id, p.name
 				FROM $t_mantis_project_table p
 				LEFT JOIN $t_mantis_project_user_list_table u
-				ON p.id=u.project_id AND u.user_id=" . db_param() . "
+				ON p.id=u.project_id AND u.user_id=$c_user_id
 				WHERE p.enabled = " . db_param() . " AND
 					u.user_id IS NULL
 				ORDER BY p.name";
-	$result = db_query_bound( $query, Array( $c_user_id, true ) );
+	$result = db_query_bound( $query, Array( true ) );
 	$category_count = db_num_rows( $result );
 	for( $i = 0;$i < $category_count;$i++ ) {
 		$row = db_fetch_array( $result );
diff --git a/core/tag_api.php b/core/tag_api.php
index 7b3bff8..57a23d8 100644
--- a/core/tag_api.php
+++ b/core/tag_api.php
@@ -418,6 +418,7 @@ function tag_get_candidates_for_bug( $p_bug_id ) {
 
 			$t_subquery_results = array();
 
+            $t_subquery_results[] = 0; # To make sure the statement will be syntactically correct even with no hits!
 			while( $row = db_fetch_array( $result ) ) {
 				$t_subquery_results[] = (int)$row;
 			}
@@ -738,9 +739,9 @@ function tag_stats_related( $p_tag_id, $p_limit = 5 ) {
 
 	$subquery = "SELECT b.id FROM $t_bug_table AS b
 					LEFT JOIN $t_project_user_list_table AS p
-						ON p.project_id=b.project_id AND p.user_id=" . db_param() . "
+						ON p.project_id=b.project_id AND p.user_id=$c_user_id
 					JOIN $t_user_table AS u
-						ON u.id=" . db_param() . "
+						ON u.id=$c_user_id
 					JOIN $t_bug_tag_table AS t
 						ON t.bug_id=b.id
 					WHERE ( p.access_level>b.view_state OR u.access_level>b.view_state )
@@ -750,7 +751,7 @@ function tag_stats_related( $p_tag_id, $p_limit = 5 ) {
 					WHERE tag_id != " . db_param() . "
 						AND bug_id IN ( $subquery ) ";
 
-	$result = db_query_bound( $query, Array( /*query*/ $c_tag_id, /*subquery*/ $c_user_id, $c_user_id, $c_tag_id ) );
+	$result = db_query_bound( $query, Array( /*query*/ $c_tag_id, /*subquery*/  $c_tag_id ) );
 
 	$t_tag_counts = array();
 	while( $row = db_fetch_array( $result ) ) {
diff --git a/core/user_api.php b/core/user_api.php
index 1b3f1ac..080a8e8 100644
--- a/core/user_api.php
+++ b/core/user_api.php
@@ -871,7 +871,7 @@ function user_get_accessible_projects( $p_user_id, $p_show_disabled = false ) {
 		$query = "SELECT p.id, p.name, ph.parent_id
 						  FROM $t_project_table p
 						  LEFT JOIN $t_project_user_list_table u
-						    ON p.id=u.project_id AND u.user_id=" . db_param() . "
+						    ON p.id=u.project_id AND u.user_id=$p_user_id
 						  LEFT JOIN $t_project_hierarchy_table ph
 						    ON ph.child_id = p.id
 						  WHERE " . ( $p_show_disabled ? '' : ( 'p.enabled = ' . db_param() . ' AND ' ) ) . "
@@ -881,7 +881,7 @@ function user_get_accessible_projects( $p_user_id, $p_show_disabled = false ) {
 							        u.user_id=" . db_param() . " )
 							)
 			  ORDER BY p.name";
-		$result = db_query_bound( $query, ( $p_show_disabled ? Array( $p_user_id, $t_public, $t_private, $p_user_id ) : Array( $p_user_id, true, $t_public, $t_private, $p_user_id ) ) );
+		$result = db_query_bound( $query, ( $p_show_disabled ? Array( $t_public, $t_private, $p_user_id ) : Array( true, $t_public, $t_private, $p_user_id ) ) );
 
 		$row_count = db_num_rows( $result );
 
@@ -950,7 +950,7 @@ function user_get_accessible_subprojects( $p_user_id, $p_project_id, $p_show_dis
 		$query = "SELECT DISTINCT p.id, p.name, ph.parent_id
 					  FROM $t_project_table p
 					  LEFT JOIN $t_project_user_list_table u
-					    ON p.id = u.project_id AND u.user_id=" . db_param() . "
+					    ON p.id = u.project_id AND u.user_id=$p_user_id
 					  LEFT JOIN $t_project_hierarchy_table ph
 					    ON ph.child_id = p.id
 					  WHERE " . ( $p_show_disabled ? '' : ( 'p.enabled = ' . db_param() . ' AND ' ) ) . '
@@ -961,7 +961,7 @@ function user_get_accessible_subprojects( $p_user_id, $p_project_id, $p_show_dis
 						        u.user_id=' . db_param() . ' )
 						)
 					  ORDER BY p.name';
-		$result = db_query_bound( $query, ( $p_show_disabled ? Array( $p_user_id, $t_public, $t_private, $p_user_id ) : Array( $p_user_id, 1, $t_public, $t_private, $p_user_id ) ) );
+		$result = db_query_bound( $query, ( $p_show_disabled ? Array( $t_public, $t_private, $p_user_id ) : Array( 1, $t_public, $t_private, $p_user_id ) ) );
 	}
 
 	$row_count = db_num_rows( $result );
diff --git a/summary_page.php b/summary_page.php
index 0ad8ca7..6a4b658 100644
--- a/summary_page.php
+++ b/summary_page.php
@@ -48,11 +48,11 @@
 	#  will look up the most recent 'resolved' status change and return it as well
 	$query = "SELECT b.id, b.date_submitted, b.last_updated, MAX(h.date_modified) as hist_update, b.status
         FROM $t_bug_table b LEFT JOIN $t_history_table h
-            ON b.id = h.bug_id  AND h.type=0 AND h.field_name='status' AND h.new_value=" . db_param() . "
+            ON b.id = h.bug_id  AND h.type=0 AND h.field_name='status' AND h.new_value=$t_resolved
             WHERE b.status >=" . db_param() . " AND $specific_where
             GROUP BY b.id, b.status, b.date_submitted, b.last_updated
             ORDER BY b.id ASC";
-	$result = db_query_bound( $query, Array( $t_resolved, $t_resolved ) );
+	$result = db_query_bound( $query, Array( $t_resolved ) );
 	$bug_count = db_num_rows( $result );
 
 	$t_bug_id       = 0;
@@ -103,7 +103,7 @@
 ?>
 
 <br />
-<?php 
+<?php
 	print_summary_menu( 'summary_page.php' );
 	print_summary_submenu(); ?>
 <br />
@@ -115,7 +115,7 @@
 </tr>
 <tr valign="top">
 	<td width="50%">
-		<?php # PROJECT # 
+		<?php # PROJECT #
 			if ( 1 < count( $t_project_ids ) ) { ?>
 		<table class="width100" cellspacing="1">
 		<tr>
-- 
1.7.3.3

sql_server_1.2.4.patch (6,950 bytes)   

Relationships

has duplicate 0013648 closedrombert "Create new account" crashes mantis 
has duplicate 0013413 closedrombert Unable to edit Users/Projects 
related to 0013906 closeddregad Application error on manage_proj_edit_page.php 

Activities

ted

ted

2011-02-07 09:03

reporter   ~0028176

The patch file contains my modifications against version 1.2.4 to get MS SQL Server 2005 running.

bikini_browser

bikini_browser

2011-02-13 08:58

reporter   ~0028213

How do you apply this patch on a Windows 2008 64bit R2 Server with IIS 7.5 and the latest version of PHP? Please advise....

Please send a reply to: contactme@daleallen.com (787) 637-9100

I am still looking for someone that is qualified to connect to my computer remotely and help me install this software. We can pay for the services with a credit card or through PayPal.

Can anyone help us here?

Dale Allen

grangeway

grangeway

2012-02-05 12:14

reporter   ~0031139

PDO layer does not have this issue

rombert

rombert

2012-02-08 13:31

reporter   ~0031197

@grangeway : I agree, and I also add that as long as we support 1.2.x - which does not use PDO - this should not be closed.

grangeway

grangeway

2014-02-17 13:26

reporter   ~0039437

Marking as Suspended

MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release.

I'd strongly advise using MYSQL for now.

The new DB layer already contains the appropriate fixes for MS SQL Support.

grangeway

grangeway

2014-03-27 19:12

reporter   ~0039767

MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release.

I'd strongly advise using MYSQL for now.

The new DB layer already contains the appropriate fixes for MS SQL Support.

grangeway

grangeway

2014-05-16 15:00

reporter   ~0040325

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul

dregad

dregad

2015-03-14 14:02

developer   ~0049224

Despite the fact that I am not actually able to test whether the problem is truly resolved since I don't have access to a MSSQL server setup, I'm marking this issue as fixed in 1.3.x on the grounds that the recent updates in ADOdb (which contain many improvements in the MSSQL driver) are likely to address it.

Should the problem persist (using a nightly build or a version > 1.3.0-beta.1), feel free to reopen this issue or to create a new one.