mssqlnative fails on Insert_ID() if the insert statement contains a semicolon in a value string

[stekule]

Hello,

the driver 'mssqlnative' fails on Insert_ID() if the insert statement contains a semicolon in a value string.
A possible solution is to replace the regular expression in ADODB_mssqlnative::_query($sql, $inputarr=false) (adodb-mssqlnative.inc.php line: 567 )

// original pattern
$pattern = "/^\W*(insert [^;]+);?$/i";

//suggested pattern which allows semicolons in value strings
$pattern = "/^\W*insert((('')*'([^'])+'('')*)|[^;'])*;?$/i";

In mssql a tick mark (') in a value string is escaped via a second one. Therefore the number of tick marks before the semicolon must be odd (since the string starts with a tick mark). In my opinion semicolons should be allowed after an odd number of tick marks.

Best regards
Stephan

sample code:

// original pattern from ADODB_mssqlnative::_query($sql, $inputarr=false) (adodb-mssqlnative.inc.php line: 567 )
//$pattern = '/^\W*(insert [^;]+);?$/i';

//suggested pattern which allows semicolon in value strings
$pattern = "/^\W*insert((('')*'([^'])+'('')*)|[^;'])*;?$/i";

echo "<h1>Pattern: ".$pattern."</h1>";

//test fixture as array of pairs (statement, assert) 
$aFixture = array(
        "insert" => 1
        , "insert dfdfs;" =>1
        , "insert tabname (param1, param2, param3) VAULES( 'Test','test','test')" =>1
        , "insert tabname (param1, param2, param3) VAULES( 'Test','te''st','test')" =>1
        , "insert tabname (param1, param2, param3) VAULES( 'Te'';st','test','test')" =>1
        , "insert tabname (param1, param2, param3) VAULES( 'Te'';st','te;st','test')" =>1
        , "insert tabname (param1, param2, param3) VAULES( 'Te'';st','te;s;t','test')" =>1
        , "insert tabname (param1, param2, param3, param4, param5 ) VAULES( 'Te'';st','te;s;t','test', 'dfsdfjfsf''fkfjdsf', 'flksfls;dfj')" =>1
        , "insert tabname (param1, param2, param3) VAULES( 'Te'';st','te;s;t','test'); insert" =>0
        , "select fdfjsfjsdl" =>0
        , "insert tabname (param1, param2, param3) VAULES( 'Test','test','test'); insert tabname (param1, param2, param3) VAULES( 'Test','test','test')" =>0
);

foreach($aFixture as $fixture => $valid){
    echo "<h2>".$fixture."</h2>";
    echo "statement ".( $valid==1 ? "should be" : "should not be"   )." allowed.";
    $aResult = array();

    $pregMatchResult = preg_match($pattern,  $fixture, $aResults);
    //var_dump($pregMatchResult);
    if($pregMatchResult === false){
        echo "<h3>Error: preg_match returns false</h3>";
        echo "<p>".preg_last_error() ."</p>";
        if (preg_last_error() == PREG_NO_ERROR) {
            print 'There is no error.';
        }
        else if (preg_last_error() == PREG_INTERNAL_ERROR) {
            print 'There is an internal error!';
        }
        else if (preg_last_error() == PREG_BACKTRACK_LIMIT_ERROR) {
            print 'Backtrack limit was exhausted!';
        }
        else if (preg_last_error() == PREG_RECURSION_LIMIT_ERROR) {
            print 'Recursion limit was exhausted!';
        }
        else if (preg_last_error() == PREG_BAD_UTF8_ERROR) {
            print 'Bad UTF8 error!';
        }
        else if (preg_last_error() == PREG_BAD_UTF8_ERROR) {
            print 'Bad UTF8 offset error!';
        }else{
            print 'Unknown error!';
        }
    }else if( $valid !== $pregMatchResult ){
        echo "<h3>Assertion failed</h3>";
    }else {
        echo "<h3>Assertion OK</h3>";
    }
    //echo "<pre>".print_r($aResults, 1)."</pre>";
    echo "<br/>";
}

[dregad]

Thanks for your contribution.

• any particular reason for removing the space after insert in your revised pattern ?
• Since we don't use the matches, I don't think there's any need to capture all of the subpatterns, performance would be improved by using non-capturing ones (i.e. (?:xxx))

Would you mind submitting a pull request for this ? This way I can give you proper credit (and reduce effort to merge the change) too.

Note: I can't test because I don't have mssql.

[stekule]

Thanks for your quick response.
It is now a pull request (https://github.com/ADOdb/ADOdb/pull/97/commits) which includes your notices.