Help us understand the problem. What is going on with this article?

PDOのプリペアドステートメントをパースするクラスを作ってみた

More than 5 years have passed since last update.

PDOのプリペアドステートメントとバインドされた値をパースして、
実際に実行されそうなクエリをエミュレートするクラスをつくってみた。

PHP内部処理の pdo_sql_parser.c と pdo_sql_parser.re を参考にしたので、それほど間違ってないと思う…。
テストしてないけど。

PDOStatementParser.php
<?php

class PDOStatementParser
{
    protected $subject = '';
    protected $result  = '';
    protected $offset  = 0;
    protected $positionalPlaceholderIndex = 0;

    /**
     * Return new PDOStatementParser object.
     */
    protected function __construct()
    {
        // protected constructor
    }

    /**
     * Parse prepared statement.
     * @static
     * @param string $statement
     * @param array $values
     * @return string
     */
    public static function parse($statement, array $values = array())
    {
        $parser = new static();
        return $parser->_parse($statement, $values);
    }

    /**
     * Parse prepared statement.
     * @param string $statement
     * @param array $values
     * @return string
     * @throws \RuntimeException
     */
    protected function _parse($statement, array $values = array())
    {
        if ( count($values) === 0 )
        {
            return $statement;
        }

        $quotation1 = '/^"([^"\\\\]*(\\\\.[^"\\\\]*)*)"/';
        $quotation2 = "/^'([^'\\\\]*(\\\\.[^'\\\\]*)*)'/";
        $bindChr    = '/^[:][a-zA-Z0-9_]+/';
        $question   = '/^[?]/';
        $comments   = "#^(/\\*([^\\*]+|\\*+[^/\\*])*\\**\\*/|--[^\r\n]*)#";
        $specials   = '/^[:?"\']/';
        $multiChar  = '/^[:?]{2,}/';
        $any        = '/^./';

        while ( $this->offset < mb_strlen($statement) )
        {
            $this->subject = substr($statement, $this->offset);

            if ( preg_match($quotation1, $this->subject, $matches) )
            {
                $this->_text($matches[0]);
            }
            elseif ( preg_match($quotation2, $this->subject, $matches) )
            {
                $this->_text($matches[0]);
            }
            elseif ( preg_match($multiChar, $this->subject, $matches) )
            {
                $this->_skipOne();
            }
            elseif ( preg_match($bindChr, $this->subject, $matches) )
            {
                $this->_bind($matches[0], $values);
            }
            elseif ( preg_match($question, $this->subject, $matches) )
            {
                $this->_bindPositional($values);
            }
            elseif ( preg_match($specials, $this->subject, $matches) )
            {
                $this->_skipOne();
            }
            elseif ( preg_match($comments, $this->subject, $matches) )
            {
                $this->_text($matches[0]);
            }
            elseif ( preg_match($any, $this->subject, $matches) )
            {
                $this->_text($matches[0]);
            }
            else
            {
                throw new RuntimeException('Parse error');
            }
        }

        return $this->result;
    }

    /**
     * Deal a text token.
     * @param string $text
     */
    protected function _text($text)
    {
        $this->result .= $text;
        $this->offset += mb_strlen($text);
    }

    /**
     * Skip a token.
     */
    protected function _skipOne()
    {
        $this->result .= $this->subject[0];
        $this->offset += 1;
    }

    /**
     * Deal a placeholder token.
     * @param string $name
     * @param string $values
     * @throws \RuntimeException
     */
    protected function _bind($name, $values)
    {
        if ( array_key_exists($name, $values) === false )
        {
            throw new RuntimeException('Placeholder doesn\'t match.');
        }

        $this->result .= $this->_valueToString($values[$name]);
        $this->offset += mb_strlen($name);
    }

    /**
     * Deal a placeholder token.
     * @param string $values
     * @throws \RuntimeException
     */
    protected function _bindPositional($values)
    {
        if ( array_key_exists($this->positionalPlaceholderIndex, $values) === false )
        {
            throw new RuntimeException('Placeholder doesn\'t match.');
        }

        $this->result .= $this->_valueToString($values[$this->positionalPlaceholderIndex]);
        $this->offset += 1;
        $this->positionalPlaceholderIndex += 1;
    }

    /**
     * Convert a value to string.
     * @param mixed $value
     * @return string
     */
    protected function _valueToString($value)
    {
        if ( $value === null )
        {
            $value = 'NULL';
        }
        elseif ( is_integer($value) === true or is_float($value) === true )
        {
            $value = strval($value);
        }
        elseif ( is_bool($value) === true )
        {
            if ( $value === true )
            {
                $value = 'TRUE';
            }
            else
            {
                $value = 'FALSE';
            }
        }
        else
        {
            $value = '\''.addslashes($value).'\''; // TODO >> use PDO->quote()
        }

        return $value;
    }
}
実行
<?php
$statement = 'SELECT * FROM table WHERE foo = :foo AND bar = :bar';
$values = array(
    ':foo' => 123,
    ':bar' => '2012-03-09',
);

$result = PDOStatementParser::parse($statement, $values);

echo $result, PHP_EOL; // SELECT * FROM table WHERE foo = 123 AND bar = '2012-03-09'
suin
Qiita 4位/TypeScript入門書執筆中/TypeScripterのための座談会「YYTypeScript」主催/『実践ドメイン駆動設計』書籍邦訳レビュア/分報Slack考案/YYPHP主催/CodeIQマガジン執筆/株式会社クラフトマンソフトウェア創設/Web自動テスト「ShouldBee」の開発/TypeScript/DDD/OOP
https://yyts.connpass.com/
craftsman_software
「インフラの心配は、もうおしまい」 インフラ運用を自動化し、手作業を限りなくゼロにする会社
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away