View Issue Details

IDProjectCategoryView StatusLast Update
0012674mantisbtdb mssqlpublic2015-03-15 19:58
ReportertedAssigned 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.

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:02

reporter  

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)
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

developer   ~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.

Issue History

Date Modified Username Field Change
2011-01-10 17:17 ted New Issue
2011-02-07 09:02 ted File Added: sql_server_1.2.4.patch
2011-02-07 09:03 ted Note Added: 0028176
2011-02-13 08:58 bikini_browser Note Added: 0028213
2011-12-05 17:50 rombert Relationship added has duplicate 0013648
2011-12-05 17:51 rombert Relationship added has duplicate 0013413
2011-12-05 17:51 rombert Status new => acknowledged
2012-02-05 12:14 grangeway Note Added: 0031139
2012-02-05 12:14 grangeway Status acknowledged => resolved
2012-02-05 12:14 grangeway Fixed in Version => 1.3.0-beta.1
2012-02-05 12:14 grangeway Resolution open => no change required
2012-02-05 12:14 grangeway Assigned To => grangeway
2012-02-08 13:31 rombert Note Added: 0031197
2012-02-08 13:31 rombert Assigned To grangeway =>
2012-02-08 13:31 rombert Status resolved => acknowledged
2012-02-08 13:31 rombert Resolution no change required => reopened
2012-02-08 13:31 rombert Fixed in Version 1.3.0-beta.1 =>
2013-08-02 03:46 atrol Relationship added related to 0013906
2013-08-16 04:38 hero2289 Tag Attached: closed
2013-08-16 06:33 atrol Tag Detached: closed
2014-02-17 13:26 grangeway Note Added: 0039437
2014-03-27 19:12 grangeway Note Added: 0039767
2014-03-27 19:12 grangeway Status acknowledged => resolved
2014-03-27 19:12 grangeway Resolution reopened => suspended
2014-03-27 19:12 grangeway Assigned To => grangeway
2014-03-28 04:18 atrol Status resolved => assigned
2014-04-12 19:33 grangeway Target Version => 1.3.0-beta.1
2014-05-16 15:00 grangeway Note Added: 0040325
2014-05-23 15:08 grangeway Project mantisbt => @24@
2014-11-07 14:29 atrol Assigned To grangeway =>
2014-11-07 14:29 atrol Status assigned => new
2014-11-07 14:29 atrol Project @24@ => mantisbt
2014-12-08 02:10 atrol Target Version 1.3.0-beta.1 => 1.3.0-beta.2
2015-03-14 14:02 dregad Note Added: 0049224
2015-03-14 14:02 dregad Status new => resolved
2015-03-14 14:02 dregad Fixed in Version => 1.3.0-beta.2
2015-03-14 14:02 dregad Resolution suspended => fixed
2015-03-14 14:02 dregad Assigned To => dregad
2015-03-15 19:58 dregad Status resolved => closed