View Issue Details

IDProjectCategoryView StatusLast Update
0022208mantisbtdb mssqlpublic2023-04-22 10:58
ReporterHaplo Assigned 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.
Attached Files

Relationships

has duplicate 0022500 closedatrol Problems with attachments stored in database when using MSSQL 
has duplicate 0007623 closeddregad Can't store binary files into MSSQL database / can't attach files to bugs 

Activities

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() ) {
file_api_fixed_upload.patch (1,192 bytes)   
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 06:32

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>
Affected Issues
0022208
mod - core/database_api.php Diff File

MantisBT: master-2.1 b9fccabf

2017-03-22 06:32

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>
Affected Issues
0022208
mod - core/database_api.php Diff File

MantisBT: master-2.2 4a06c6e8

2017-03-22 06:32

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>
Affected Issues
0022208
mod - core/database_api.php Diff File