Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Bug Reports » Semicolon in Strings within SQL Multi Query String
Semicolon in Strings within SQL Multi Query String [message #4610] Sun, 26 October 2014 15:10 Go to next message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
When calling function executeQuery($query), it passes $query to the function multiQuery ($dbname, $tablename, $query) in dml.mysqli.class.inc, which uses the semicolon as the deliminator to split up $query into sub-queries for execution by MySQL.

However, this will result in incorrect sub-queries when sub-queries contain strings that themselves contain semicolon characters.

To solve this problem, we need to distinguish between semicolons that are part of strings and semicolons that delimit SQL sub-queries. Hence, would like to propose to make below changes in the source code of multiQuery function in dml.mysqli.class.inc.

Change from
            if (!is_array($query)) {
                // split string into an array of individual queries
                $array = explode(';', $query);
                $query = '';
                foreach ($array as $value) {
                    if (!empty($value) AND substr($value, -1, 1) != ';') {
                        $query[] = $value.';';  // replace query terminator
                    } // if
                } // foreach
            } // if

To
            if (!is_array($query)) {
                // split string into an array of individual queries
                $array = explode(';', $query); 
                $query = '';
                $incomplete_query = '';
                foreach ($array as $value) {
              		$incomplete_query .= TRIM($value);                		             
                	if (substr_count(str_replace("\'",'',$incomplete_query),"'")%2) {
                		// Odd number single quotes means semicolon was part of a string in one SQL sub-query
                		// Must add back the semicolon and loop to restore the remainder of SQL sub-query
                		$incomplete_query .=  ';'; 
                	} // if
                	elseif (!empty($incomplete_query)) {
                                $query[] = $incomplete_query.';';
                		$incomplete_query = '';
                	} // elseif
                } // foreach               
            } // if

Have not checked whether this problem existed for the other database engines.
Re: Semicolon in Strings within SQL Multi Query String [message #4613 is a reply to message #4610] Mon, 27 October 2014 08:48 Go to previous messageGo to next message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
Noticed a bug in my previous solution, which did not take into account spaces followed by semicolon within strings inside sub-queries.
So, here is the updated version.
            if (!is_array($query)) {
                // split string into an array of individual queries
                $array = explode(';', $query); 
                $query = '';
                $sub_query = '';
                foreach ($array as $value) {            	
                	$sub_query .= $value;             
                	if (substr_count(str_replace("\'",'',$sub_query),"'")%2) 
                        {
                		// Odd number single quotes means semicolon was part of a string in one sub-query
                		// Must add back the semicolon and loop to restore the remainder of sub-query
                		$sub_query .= ';';
                	} // if
                	else 
                        {
                		$sub_query = trim($sub_query);              		
                       	        if (!empty($sub_query)) {
                        	        $query[] = $sub_query.';';   // replace query terminator
                			$sub_query = '';
                     	        } // if
                     	        // $sub_query is empty again ready for the next $value
                	} // else
                } // foreach                
            } // if
Re: Semicolon in Strings within SQL Multi Query String [message #4614 is a reply to message #4613] Mon, 27 October 2014 11:25 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
I have found a way to do this with a regular expression which I have inserted into 'std.table.class.inc'. This turns the query into an array before it is passed to 'dml.mysqli.class.inc'. Try it out to see if it works OK for you.

Re: Semicolon in Strings within SQL Multi Query String [message #4615 is a reply to message #4614] Mon, 27 October 2014 18:03 Go to previous message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
Tested and looks good so far. Thanks!
Previous Topic: Unexpected "Cannot extract token from" Error Due to Tabs / Newlines
Next Topic: Duplicated Messages on SUBMIT+Stay (File Upload pattern)
Goto Forum:
  


Current Time: Thu Mar 28 12:12:02 EDT 2024

Total time taken to generate the page: 0.01142 seconds