View Issue Details

IDProjectCategoryView StatusLast Update
0022208mantisbtdb mssqlpublic2017-04-01 00:13
ReporterHaploAssigned Todregad 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version1.3.0-beta.1 
Target Version1.3.9Fixed in Version1.3.9 
Summary0022208: File upload to MS-SQL not working
Description

The file upload to the MS SQL database isn't working.
The file is always an empty file with no data.

To fix the Problem I've integrated the changes proposed by another user (don't remember who it was, sorry) and with it it is working.

I've added my used MS SQL base script and the fixed file_api.php

Additional Information

Mantis 2.0.0
IIS
MS-SQL

TagsNo tags attached.

Relationships

has duplicate 0022500 closedatrol Problems with attachments stored in database when using MSSQL 

Activities

Haplo

Haplo

2017-01-16 04:06

reporter  

BugTracker_Backup_-_BaseScript_20161216.zip (13,401 bytes)
dregad

dregad

2017-01-16 05:13

developer   ~0055144

The attached patch file replaces the originally uploaded PHP file (file_api__fixed_upload_200.php).

I had a quick look at the code (but didn't test as I don't have access to MSSQL). Looks like you're replacing a query parameter by inline value, so this is probably not the right fix for the problem. Probably better look into why the file contents are not being inserted.



file_api_fixed_upload.patch (1,192 bytes)
diff --git a/core/file_api.php b/core/file_api.php
index fef6404..4d816fd 100644
--- a/core/file_api.php
+++ b/core/file_api.php
@@ -795,7 +795,7 @@ function file_add( $p_bug_id, array $p_file, $p_table = 'bug', $p_title = '', $p
 		'user_id'     => (int)$p_user_id,
 	);
 	# Oracle has to update BLOBs separately
-	if( !db_is_oracle() ) {
+	if( !db_is_oracle() && !db_is_mssql() ) {
 		$t_param['content'] = $c_content;
 	}
 	$t_query_param = db_param();
@@ -803,10 +803,18 @@ function file_add( $p_bug_id, array $p_file, $p_table = 'bug', $p_title = '', $p
 		$t_query_param .= ', ' . db_param();
 	}
 
-	$t_query = 'INSERT INTO ' . $t_file_table . '
-		( ' . implode(', ', array_keys( $t_param ) ) . ' )
-	VALUES
-		( ' . $t_query_param . ' )';
+	if ( !db_is_mssql() ) {
+		$t_query = 'INSERT INTO ' . $t_file_table . '
+			( ' . implode(', ', array_keys( $t_param ) ) . ' )
+		VALUES
+			( ' . $t_query_param . ' )';
+	} else {
+		$t_query = 'INSERT INTO ' . $t_file_table . '
+			( ' . implode(', ', array_keys( $t_param ) ) . ', content  )
+		VALUES
+			( ' . $t_query_param . ', ' . $c_content . ' )';
+	}
+
 	db_query( $t_query, array_values( $t_param ) );
 
 	if( db_is_oracle() ) {
Haplo

Haplo

2017-01-16 06:21

reporter   ~0055145

I'm also not an PHP/SQL specialist, so am maybe not the right person for detailed php/SQL questions.
As far as I understand it, is the problem, that the filedata isn't converted correctly when using them via params. But if you add the filedata directly, with bypassing of the params, then it works.
Why, or how? I also don't know :-)

obmsch

obmsch

2017-01-16 10:02

reporter   ~0055154

@Haplo Did you check the 'bug_file_table' if the content field is really empty? I had an issue
with the stored content, but not been empty at all.

file_add(file_api.php) calls db_prepare_binary_string(database_api.php) for method=DATABASE,
and in case of mssql this uses "unpack( 'H*hex', .." to modify the content. I didn't check
if this is not "reversed" when the file is downloaded or viewed inline.

I simply commented out "case 'mssqlnative':" in that function to get it work, at least in my
environment (SQLServer Express 2012, PHP 7.0.14, MS PHP Drivers for SQL Server PHP7 V4.1.4) the
"unpack" isn't necessary any more.

Haplo

Haplo

2017-01-17 03:11

reporter   ~0055176

No sorry, I didn't actually check if it really was empty or not.
The result was that the uploaded file (tested with pictures) couldn't be viewed anymore.
So, yes, it could be that the problem is inside the db_prepare_binary_string(..) function.

Haplo

Haplo

2017-01-17 03:51

reporter   ~0055178

I did a quick test for this and it looks like that is the problem.
When I change the function to return the default value in the database_api.php the upload/redownload works on my side. :)

Here is the changed function I used:

function db_prepare_binary_string( $p_string ) {
global $g_db;
$t_db_type = config_get_global( 'db_type' );

switch( $t_db_type ) {
    case 'mssqlnative':
        return $p_string;
        break;
    case 'mssql':
    case 'odbc_mssql':
    case 'ado_mssql':
        $t_content = unpack( 'H*hex', $p_string );
        return '0x' . $t_content['hex'];
        break;
    case 'postgres':
    case 'postgres64':
    case 'postgres7':
    case 'pgsql':
        return $g_db->BlobEncode( $p_string );
        break;
    case 'oci8':
        # Fall through, oci8 stores raw data in BLOB
    default:
        return $p_string;
        break;
}

}

vkall

vkall

2017-01-17 07:02

reporter   ~0055183

I also had problems with File upload when upgrading an old Mantis installation to 2.0 (With MSSQL as our database).

I noticed that the files uploaded become hex encoded twice. So to fix it I commented out this line.
https://github.com/mantisbt/mantisbt/blob/release-2.0.0/core/database_api.php#L782

It is essentially the same fix as Haplo posted above.

abeardsall

abeardsall

2017-01-26 09:43

reporter   ~0055336

Had this problem as well, thank-you for posting the fix Haplo!

Haplo

Haplo

2017-02-08 02:27

reporter   ~0055572

The file upload is still not working in 2.1.0.
But the last fix I posted ( db_prepare_binary_string ) still works :)

obmsch

obmsch

2017-03-22 11:34

reporter   ~0056162

PR: https://github.com/mantisbt/mantisbt/pull/1062

dregad

dregad

2017-03-31 16:28

developer   ~0056304

Backported to 1.3.x and applied to supported 2.x branches. Thanks @obmsch

Related Changesets

MantisBT: master-1.3.x 43c4b6fb

2017-03-22 10:32:30

obmsch


Committer: dregad Details Diff
mssql: don't encode contents when uploading attachments

Downloads are broken and inline preview doesn't work.

Move "case 'mssqlnative':" down next to 'oci8' in
db_prepare_binary_string (database_api) to effectively
return the string unchanged via 'default:'. Adjust comment.

Fixes 0022208

Signed-off-by: Damien Regad <dregad@mantisbt.org>
mod - core/database_api.php Diff File

MantisBT: master-2.1 b9fccabf

2017-03-22 10:32:30

obmsch


Committer: dregad Details Diff
mssql: don't encode contents when uploading attachments

Downloads are broken and inline preview doesn't work.

Move "case 'mssqlnative':" down next to 'oci8' in
db_prepare_binary_string (database_api) to effectively
return the string unchanged via 'default:'. Adjust comment.

Fixes 0022208

Signed-off-by: Damien Regad <dregad@mantisbt.org>
mod - core/database_api.php Diff File

MantisBT: master-2.2 4a06c6e8

2017-03-22 10:32:30

obmsch


Committer: dregad Details Diff
mssql: don't encode contents when uploading attachments

Downloads are broken and inline preview doesn't work.

Move "case 'mssqlnative':" down next to 'oci8' in
db_prepare_binary_string (database_api) to effectively
return the string unchanged via 'default:'. Adjust comment.

Fixes 0022208

Signed-off-by: Damien Regad <dregad@mantisbt.org>
mod - core/database_api.php Diff File

Issue History

Date Modified Username Field Change
2017-01-16 04:06 Haplo New Issue
2017-01-16 04:06 Haplo File Added: BugTracker_Backup_-_BaseScript_20161216.zip
2017-01-16 04:06 Haplo File Added: file_api__fixed_upload_200.php
2017-01-16 04:23 dregad Status new => acknowledged
2017-01-16 04:23 dregad Category sql => db mssql
2017-01-16 04:23 dregad Description Updated View Revisions
2017-01-16 04:23 dregad Additional Information Updated View Revisions
2017-01-16 04:50 dregad File Deleted: file_api__fixed_upload_200.php
2017-01-16 05:13 dregad File Added: file_api_fixed_upload.patch
2017-01-16 05:13 dregad Note Added: 0055144
2017-01-16 06:21 Haplo Note Added: 0055145
2017-01-16 10:02 obmsch Note Added: 0055154
2017-01-17 03:11 Haplo Note Added: 0055176
2017-01-17 03:51 Haplo Note Added: 0055178
2017-01-17 07:02 vkall Note Added: 0055183
2017-01-26 09:43 abeardsall Note Added: 0055336
2017-02-08 02:27 Haplo Note Added: 0055572
2017-03-09 10:02 atrol Relationship added has duplicate 0022500
2017-03-22 11:34 obmsch Note Added: 0056162
2017-03-31 16:26 dregad Changeset attached => MantisBT master-1.3.x 43c4b6fb
2017-03-31 16:26 dregad Assigned To => dregad
2017-03-31 16:26 dregad Status acknowledged => resolved
2017-03-31 16:26 dregad Resolution open => fixed
2017-03-31 16:26 dregad Fixed in Version => 1.3.9
2017-03-31 16:26 dregad Changeset attached => MantisBT master-2.1 b9fccabf
2017-03-31 16:26 dregad Changeset attached => MantisBT master-2.2 4a06c6e8
2017-03-31 16:28 dregad Product Version 2.0.0 => 1.3.0-beta.1
2017-03-31 16:28 dregad Target Version => 1.3.9
2017-03-31 16:28 dregad Note Added: 0056304
2017-04-01 00:13 vboctoradmin Status resolved => closed