FreenetIS

<?php

/*

* This file is part of open source system FreenetIS

* and it is release under GPLv3 licence.

*

* More info about licence can be found:

* http://www.gnu.org/licenses/gpl-3.0.html

*

* More info about project can be found:

* http://www.freenetis.org/

*/

/**

* SQL script file parser for parsing of SQL queries passed as single string

* delimited by semicolons. Comment are started using "--".

*

* @author Ondřej Fibich <fibich@freenetis.org>

* @since 1.2

*/

class SqlScriptParser

{

// states for finite automata that is used for parsing

/** In query state */

const STATE_QUERY = 0;

/** In query string state */

const STATE_STRING = 2;

/** In query string escaped character state */

const STATE_STRING_ESCAPE = 3;

/** In maybe comment state */

const STATE_COMMENT_START = 4;

/** In comment state */

const STATE_COMMENT = 5;

/**

* Parse SQL queries that must be ended by semicolons.

*

* @param string $script_content SQL script file content

* @return array parsed queries, each query is trimed for white spaces

* @throws InvalidArgumentException on invalid script content

*/

public function parse_queries($script_content)

{

if (!is_string($script_content) || empty($script_content))

{

return array();

}

$s = self::STATE_QUERY; // FA status

$queries = array(); // parsed queries (result)

$buffer = array(); // help letter buffer

$string_term = NULL; // last string termination character (" or ')

$script_length = mb_strlen($script_content);

for ($i = 0; $i < $script_length; $i++)

{

$letter = $script_content[$i];

switch ($s)

{

case self::STATE_QUERY:

case self::STATE_COMMENT_START:

$s = $this->fa_in_query($s, $letter, $buffer, $queries,

$string_term);

break;

case self::STATE_STRING:

$s = $this->fa_in_string($letter, $buffer, $string_term);

break;

case self::STATE_STRING_ESCAPE:

$buffer[] = $letter;

$s = self::STATE_STRING; // only one letter after \

break;

case self::STATE_COMMENT:

$s = $this->fa_in_comment($letter);

break;

default:

throw new Exception('Unhandled FA status: ' . $s);

}

}

return $queries;

}

private function fa_in_query($status, $letter, array &$buffer,

array &$queries, &$string_term)

{

switch ($letter)

{

case '-':

if ($status === self::STATE_COMMENT_START)

{

array_pop($buffer); // remove -

return self::STATE_COMMENT;

}

$buffer[] = $letter;

return self::STATE_COMMENT_START;

case '"':

case '\'':

$buffer[] = $letter;

$string_term = $letter;

return self::STATE_STRING;

case ';': // query end

if (empty($buffer))

{

throw new InvalidArgumentException('empty SQL query parsed');

}

$queries[] = trim(implode('', $buffer));

$buffer = array();

return self::STATE_QUERY;

default:

$buffer[] = $letter;

return $status;

}

}

private function fa_in_string($letter, array &$buffer, &$string_term)

{

$buffer[] = $letter;

switch ($letter)

{

case '\\':

return self::STATE_STRING_ESCAPE;

case $string_term:

$string_term = NULL;

return self::STATE_QUERY;

default:

return self::STATE_STRING;

}

}

private function fa_in_comment($letter)

{

return ($letter === "\n") ? self::STATE_QUERY : self::STATE_COMMENT;

}

}

<?php

/*

* This file is part of open source system FreenetIS

* and it is release under GPLv3 licence.

*

* More info about licence can be found:

* http://www.gnu.org/licenses/gpl-3.0.html

*

* More info about project can be found:

* http://www.freenetis.org/

*/

/**

* Test case for SqlScriptParser class.

*/

class SqlScriptParserTest extends PHPUnit_Framework_TestCase

{

private static $test_data = array

(

// missing ;

" SELECT * FROM a " => array(),

// one query

" SELECT * FROM a \n ; " => array("SELECT * FROM a"),

// one query with "string"

'SELECT "; \"";' => array('SELECT "; \""'),

// one query with 'string'

"SELECT 'sa; \\'aa';" => array("SELECT 'sa; \\'aa'"),

// two query

"SELECT 1;SELECT 2;SELECT 3" => array('SELECT 1', 'SELECT 2'),

// two query with comment

"SELECT 1;--SELECT 2;SELECT 3\nSELECT 4;--a" =>

array('SELECT 1', 'SELECT 4'),

// query separated by comment

"SELECT 1, -- 1 value\n2;" => array("SELECT 1, 2"),

// two query with comment ans WS

" SELECT 1 ; --SELECT 2;SELECT 3\nSELECT 4;------------a\n " =>

array('SELECT 1', 'SELECT 4'),

);

/**

* @var SqlScriptParser

*/

protected $object;

protected function setUp()

{

$this->object = new SqlScriptParser;

}

/**

* @covers freenetis\service\core\SqlScriptParser::parse_queries

*/

public function testParse_queries()

{

// invalid arg

$this->assertEquals(array(), $this->object->parse_queries(NULL));

$this->assertEquals(array(), $this->object->parse_queries(FALSE));

$this->assertEquals(array(), $this->object->parse_queries(array()));

// empty SQL query

try

{

$this->object->parse_queries('; ');

$this->fail('should throw InvalidArgumentException');

}

catch (\InvalidArgumentException $ex)

{

}

// single query no semi-colon

foreach (self::$test_data as $query => $exp)

{

$result = $this->object->parse_queries($query);

$this->assertEquals(\count($exp), \count($result), $query);

for ($i = 0; $i < \count($result); $i++)

{

$this->assertEquals($exp[$i], $result[$i], $query);

}

}

}

}