To Prepare SQL Statements and Read Data

試作一個 PHP 程式，可以從用戶端取得使用者輸入的資料，接著讀入一份外部的 SQL 指令稿。將 PHP 中的變數值代入 SQL 指令之中 (prepares SQL statements)，以之查詢資料庫取回查詢結果。

test.sql

測試案例。這是一份 SQL 指令稿，採用標準 SQL 語法定義變數，並代入查詢敘述中。這份指令稿中包含了兩道查詢敘述，故會得到兩份結果表。

DECLARE @start_date varchar(10);
SET @start_date = '2007-02-01';

SELECT * FROM orders
WHERE sdate = @start_date AND edate = @end_date AND note LIKE @note;

SELECT * FROM customers WHERE customer = @customer_id

第一階段試作

利用 preg_match_all() 找出 SQL 敘述中的變數內容，判斷 PHP 程序中有無定義同名變數(16行)。分別建立兩份表，一份是要代換的變數名稱($patterns)，一份是變數值($replacements)。接著以 preg_replace() 完成 SQL 敘述的變數代換動作。

<?php
$start_date = '2007-01-01'; //假設這些變數是使用者輸入的資料

$end_date = '2007-04-30';
$customer_id = 101;

$sheetsFilePath = 'test.sql'; //外部 SQL 指令稿


$s = file_get_contents($sheetsFilePath);

preg_match_all('/@(\w+)/', $s, $m);
print_r($m[1]);

$patterns = array();
$replacements = array();
foreach ($m[1] as $name) {
    if (!isset($$name))
        continue; /*Not Replace*/
    $patterns[$name] = '/@' . $name . '([^\w])/';
    $replacements[$name] = (is_numeric($$name) ? $$name : "'{$$name}'") . '$1';
}

print_r($patterns);
print_r($replacements);

/*
注意: 有一種符合 REGEX 語意但不合 SQL 語意的替代情形，即 @var 被包括在
單引號中的情形。
按 SQL 語意，單引號中的 @var 被視為字串內容而非變數。
按 REGEX 語意，仍然符合替代條件而替換為變數值。
*/
$sqlQuery = preg_replace($patterns, $replacements, $s);
echo $sqlQuery;
?>

改成函數形式(sql_sheets_read.php)

將上述的直述程式重整為函數形式。將每一道查詢指令之結果視為一個試算表($sheet)，存入一個陣列中。最後傳回所有結果的總表($sheets)。

<?php
function &sql_sheets_read($pdoDb, $sqlFilePath, $sqlParameters) {
    if (constant('INTERNAL_TEST'))
        global $testCaseSet;
    $rcFalse = false;

    if (!($s = file_get_contents($sqlFilePath)))
        return $rcFalse;

    preg_match_all('/SELECT [^;]+/i', $s, $sqlSet);  //分析查詢指令個數

    //print_r($sqlSet);


    if (get_magic_quotes_gpc()) {
        foreach ($sqlParameters as $k => &$v) {
            $v = addslashes($v);
        }
    }
    //print_r($sqlParameters);


    $sheets = array();  //每一道查詢指令之結果將分別存在不同的表格

    $indexOfSheets = 0;
    foreach ($sqlSet[0] as &$sqlText) {
        preg_match_all('/@(\w+)/', $sqlText, $m);
        //print_r($m[1]);


        $patterns = array();
        $replacements = array();
        foreach ($m[1] as &$name) {
            if (!isset($sqlParameters[$name]))
                continue; /*Not Replace*/
            $patterns[$name] = '/@' . $name . '([^\w]|\b)/';
            $replacements[$name] = (
                is_numeric($sqlParameters[$name])
                    ? $sqlParameters[$name]
                    : "'{$sqlParameters[$name]}'"
                ) . '$1';
        }
        //print_r($patterns);

        //print_r($replacements);


        /*
        注意: 有一種符合 REGEX 語意但不合 SQL 語意的替代情形，即 @var 被包括在
        單引號中的情形。
        按 SQL 語意，單引號中的 @var 被視為字串內容而非變數。
        按 REGEX 語意，仍然符合替代條件而替換為變數值。
        */
        $sqlQuery = preg_replace($patterns, $replacements, $sqlText) . ';';
        //echo $sqlQuery, "\n";

        if (constant('INTERNAL_TEST'))
            PHPUnit_Framework_Assert::assertEquals($sqlQuery,
                $testCaseSet['sql_sheets_read'][$indexOfSheets]);

        $sheet = &$sheets[$indexOfSheets];
        foreach ($pdoDb->query($sqlQuery) as &$row) {
            $sheet[] = $row;
        }
        ++$indexOfSheets;
    }
    return $sheets;
}
?>

函數測試案例

測試案例使用 PHPUnit3 做為單元測試工具。由於要測試的內容位於函數之中，所以此處採用 PHPUnit 用例中較少見的測試作法。

<?php
define('INTERNAL_TEST', true);
require 'PHPUnit/Framework.php';
require 'sql_sheets_read.php';

$sp['start_date'] = '2007-01-01';
$sp['end_date'] = '2007-04-30';
$sp['customer_id'] = 101;
$sp['note'] = "';SELECT * FROM users";

$testCaseSet = array(
    'sql_sheets_read' => array(
    "SELECT * FROM orders
WHERE sdate = '2007-01-01' AND edate = '2007-04-30' AND note LIKE '\';SELECT * FROM users';"
    ,
    "SELECT * FROM customers WHERE customer = 101
;"
    )
);

$sheetsFilePath = 'test.sql';

$pdoDb = new PDO('pgsql:host=localhost port=5432 dbname=testdb user=rock password=mypass');
sql_sheets_read($pdoDb, $sheetsFilePath, $sp);
?>

樂多舊網址: http://blog.roodo.com/rocksaying/archives/3321763.html