0010488: Inserting strings > 4000 Bytes not working -> direct file upload and email - MantisBT - Signup temporarily disabled due to spam

ID	Project	Category	View Status	Date Submitted	Last Update

0010488	mantisbt	db oracle	public	2009-05-14 10:54	2014-12-08 00:34

Reporter	msierszen	Assigned To	dregad
Priority	normal	Severity	major	Reproducibility	always
Status	closed	Resolution	fixed
Product Version	1.1.7
Target Version	1.3.0-beta.1	Fixed in Version	1.3.0-beta.1

Summary	0010488: Inserting strings > 4000 Bytes not working -> direct file upload and email
Description	The direct file upload using "INSERT ..." statement is not working with oracle as there is a 4000 Bytes size limit. This problem occurs as well in email_queue. Solution: Use function UpdateBlob() See patchfile for details.
Tags	oracle, patch
Attached Files	mantis_fileupload_1.1.7-2.patch (4,046 bytes) diff -rupN mantisbt-1.1.7/core/email_queue_api.php mantisbt-1.1.7_zce/core/email_queue_api.php --- mantisbt-1.1.7/core/email_queue_api.php 2009-04-20 09:14:00.000000000 +0200 +++ mantisbt-1.1.7_zce/core/email_queue_api.php 2009-05-13 11:16:17.053875000 +0200 @@ -62,6 +62,7 @@ # -------------------- function email_queue_add( $p_email_data ) { + global $g_db; $t_email_data = email_queue_prepare_db( $p_email_data ); # email cannot be blank @@ -89,22 +90,44 @@ $c_body = $t_email_data->body; $c_metadata = serialize( $t_email_data->metadata ); - $query = "INSERT INTO $t_email_table - ( email, - subject, - body, - submitted, - metadata) - VALUES - ( '$c_email', - '$c_subject', - '$c_body', - " . db_now() . ", - '$c_metadata' - )"; - db_query( $query ); - - return db_insert_id( $t_email_table ); + $t_db_type = config_get( 'db_type' ); + if($t_db_type == 'oci8') { + $query = "INSERT INTO $t_email_table + ( email, + subject, + body, + submitted, + metadata) + VALUES + ( '$c_email', + '$c_subject', + empty_clob(), + " . db_now() . ", + '$c_metadata' + )"; + db_query( $query ); + + $id = db_insert_id($t_email_table); + $g_db->UpdateClob($t_email_table,'body', $c_body, "EMAIL_ID = $id"); + } else { + $query = "INSERT INTO $t_email_table + ( email, + subject, + body, + submitted, + metadata) + VALUES + ( '$c_email', + '$c_subject', + '$c_body', + " . db_now() . ", + '$c_metadata' + )"; + db_query( $query ); + $id = db_insert_id( $t_email_table ); + } + + return $id; } # -------------------- diff -rupN mantisbt-1.1.7/core/file_api.php mantisbt-1.1.7_zce/core/file_api.php --- mantisbt-1.1.7/core/file_api.php 2009-04-20 09:14:00.000000000 +0200 +++ mantisbt-1.1.7_zce/core/file_api.php 2009-05-13 11:17:27.757000000 +0200 @@ -571,6 +571,7 @@ document.getElementById( span ).style.di # -------------------- function file_add( $p_bug_id, $p_tmp_file, $p_file_name, $p_file_type='', $p_table = 'bug', $p_file_error = 0, $p_title = '', $p_desc = '' ) { + global $g_db; if ( php_version_at_least( '4.2.0' ) ) { switch ( (int) $p_file_error ) { @@ -678,11 +679,22 @@ document.getElementById( span ).style.di $t_file_table = config_get( 'mantis_' . $p_table . '_file_table' ); $c_id = ( 'bug' == $p_table ) ? $c_bug_id : $c_project_id; - $query = "INSERT INTO $t_file_table - (" . $p_table . "_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content) - VALUES - ($c_id, '$c_title', '$c_desc', '$c_disk_file_name', '$c_new_file_name', '$c_file_path', $c_file_size, '$c_file_type', " . db_now() .", $c_content)"; - db_query( $query ); + $t_db_type = config_get( 'db_type' ); + if($t_db_type == 'oci8') { + $query = "INSERT INTO $t_file_table + (" . $p_table . "_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content) + VALUES + ($c_id, '$c_title', '$c_desc', '$c_disk_file_name', '$c_new_file_name', '$c_file_path', $c_file_size, '$c_file_type', " . db_now() .", empty_blob())"; + db_query( $query ); + $id = db_insert_id($t_file_table); + $g_db->UpdateBlobFile($t_file_table,'content', $p_tmp_file, "ID = $id",'BLOB'); + } else { + $query = "INSERT INTO $t_file_table + (" . $p_table . "_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content) + VALUES + ($c_id, '$c_title', '$c_desc', '$c_disk_file_name', '$c_new_file_name', '$c_file_path', $c_file_size, '$c_file_type', " . db_now() .", $c_content)"; + db_query( $query ); + } if ( 'bug' == $p_table ) { # updated the last_updated date mantis_fileupload_1.1.7-2.patch (4,046 bytes)

msierszen 2009-07-31 08:00 reporter ~0022633	Use the second patch. The first patch file had a bug. Please delete it.

ViperNet 2009-07-31 12:03 reporter ~0022637 Last edited: 2009-08-08 09:05	wherever a CLOB field is inserted, or updated, take care to use the "UpdateClob()" function with the right "where" clause parameter. for example in email_queue.php, the "where" clause parameter will look like: "EMAIL_ID = $id" in mantis_config_table there is a 'primary key' with 3 fields: 'config_id', 'project_id', 'user_id' so in "config_api.php" inthe config_set() function you must add a new row below the "$result = db_query( $t_set_query );": $g_db->UpdateClob($t_config_table,'VALUE', $c_value, "config_id = '$c_option' AND project_id = $c_project AND user_id = $c_user");

damien_b 2009-08-11 09:26 reporter ~0022714	Blob is not needed, here is what we do: in email_queue_api $query = "INSERT INTO $t_email_table ( email, subject, body, submitted, metadata) VALUES ( '$c_email', '$c_subject', :body, " . db_now() . ", '$c_metadata' )"; $params = array('body' => $c_body); db_query( $query, -1, -1, $params ); db_query must be modified as well, in database_api.php: function db_query( $p_query, $p_limit = -1, $p_offset = -1, $val = false ) { global $g_queries_array, $g_db; `$t_start = microtime_float(); if ( ( $p_limit != -1 ) \|\| ( $p_offset != -1 ) ) { $t_result = $g_db->SelectLimit( $p_query, $p_limit, $p_offset, $val ); } else { $t_result = $g_db->Execute( $p_query, $val); }`

vboctor 2009-10-29 00:12 manager ~0023468	I'm wondering if this is resolved in 1.2.x since we now use db_query_bound() `$query = "INSERT INTO $t_email_table ( email, subject, body, submitted, metadata) VALUES ( " . db_param() . ", " . db_param() . ", " . db_param() . ", " . db_param() . ", " . db_param() . " )"; db_query_bound( $query, Array( $c_email, $c_subject, $c_body, db_now(), $c_metadata ) );`

damien_b 2009-10-29 09:48 reporter ~0023472	It's too much work for us to try to migrate to 1.2.x right now (we haven't even finished to cleanly extract our patches from 1.1.x), but reading the Param implementation in the oci8 ADOdb driver, that should indeed work.

watergad 2009-10-29 16:22 reporter ~0023475	Whatever I do with 1.1.8 and 1.2.0 it doesn't work exactly because of var bindings ( 0010996 ). Oracle 9i.