如何把sql语句转换成预处理的sql语句？

Question

老的项目中sql语句都是拼接的
$sql = sprintf("select * from t where name='%s'",$name);
有什么办法能改成预处理模式吗？
$sql = sprintf("select * from t where name=?");
$pre=['s',$name];
项目中的sql语句非常多，手工每条修改几乎不现实，有没有办法或者什么方法可以做到呢？

Answer 1

我有个思路, sql就是增删改查四种,
PHP有个 token_get_all 函数, 可以把PHP文件识别成词法数组,
在词法数组中, 判断每个类型为字符串的token, 看看里面有没有select, insert,...这些关键字, 如果有, 就把后面的参数加个 $pdo->quote() 转义.
搜一搜项目里的用法, 进行前后token的判断.
字符串也就 sprintf, 用.接拼, 发现别的再添加处理条件.

以前我用这种方式处理过模板里的xss问题, 把输出全进行html转义了.
有误判, 比较少.

Answer 2

用 PHP-Parser 遍历你的代码。

PHP-Parser/Walking_the_AST.markdown at 4.x · nikic/PHP-Parser

---

这里写了一个，需要安装 nikic/php-parser，不过这个也有一些问题，因为 最终的结果的代码是经过重新格式化的了，虽然效果能达到了，但是代码却被重新格式化过了 😑，如果项目之前没有使用 PHPCS Fixer 之类的工具来统一格式的话，就比较麻烦， Git 上就会看到有很多变更记录了。

另外，代码里面只是写死的代码，要达到可用状态，你还需要自己遍历文件，可以使用 symfony/finder。

<?php

use PhpParser\Node;
use PhpParser\NodeTraverser;
use PhpParser\NodeVisitor\ParentConnectingVisitor;
use PhpParser\NodeVisitorAbstract;
use PhpParser\Parser;
use PhpParser\ParserFactory;
use PhpParser\PrettyPrinter\Standard;

require __DIR__ . '/vendor/autoload.php';

// 要解析的代码
$code = <<<'PHP'
<?php
    function foo(){
        $sql = sprintf("select * from t where name='%s' and age = '%d'",$name,1);
        $sql = sprintf("select * from t where name2='%s'",$name);
    }

    $sql = sprintf("select * from t where name='%s' and age = '%d'",$name,'xx');
    $sql = sprintf("select * from t where name2='%s'",$name);
PHP;


$parser = (new ParserFactory)->create(ParserFactory::PREFER_PHP7);
try {
    $ast = $parser->parse($code);
} catch (Error $error) {
    echo "Parse error: {$error->getMessage()}\n";
    return;
}

$prettyPrinter = new Standard;

$traverser = new NodeTraverser();
$traverser->addVisitor(new ParentConnectingVisitor());
$traverser->addVisitor(
    new class($parser, $prettyPrinter) extends NodeVisitorAbstract {
        /**
         * @var Parser
         */
        private $parser;

        /**
         * @var Standard
         */
        private $printer;

        public function __construct($parser, $printer)
        {
            $this->parser = $parser;
            $this->printer = $printer;
        }

        public function leaveNode(Node $node)
        {
            $rootNode = false;
            if ($node instanceof Node\Stmt && property_exists($node, 'stmts')) {
                $stmts = &$node->stmts;
            } elseif ($rootNode = $this->isSqlAssignVariable($node)) {
                $stmts = [$node];
                $rootNode = true;
            } elseif (($parent = $node->getAttribute('parent')) === null || is_array($parent)) {
                $stmts = $parent ?? [$node];
            } elseif (property_exists($parent, 'stmts') && is_array($parent->stmts)) {
                $stmts = &$parent->stmts;
            }

            if (empty($stmts)) {
                return;
            }

            $oNode = $node;
            array_walk($stmts, function ($node, $index) use (&$stmts) {
                if (!($this->isSqlAssignVariable($node))) {
                    return;
                }
                $args = $node->expr->expr->args;
                $sql = trim($node->expr->expr->args[0]->value->value, ' ');
                $node->expr->expr = new Node\Scalar\String_(
                    trim(
                        preg_replace_callback(
                            $pattern = '/(([\'"]?)(?<!%)%(?:\.\d+)?[sdf]\2)/',
                            static function ($matches) {
                                return ' ? ';
                            },
                            $sql
                        )
                        ,
                        ' '
                    )
                );
                $matched = preg_match_all($pattern, $sql, $matches);
                if (!$matched) {
                    return;
                }
                $placeholders = $matches[1];

                $placeholders = array_map(static function ($v) {
                    return trim($v, '"\'%')[0] ?? '';
                }, $placeholders);
                $placeholders = array_filter($placeholders);
                $sprintfToPrepareType = [
                    'd' => 'i',
                    'f' => 'd'
                ];

                $placeholders = array_map(static function ($v) use ($sprintfToPrepareType) {
                    return $sprintfToPrepareType[$v] ?? $v;
                }, $placeholders);
                $args = array_slice($args, 1);
                if (!$args || count($placeholders) !== count($args)) {
                    return;
                }
                $newArgs = array_map(function (Node\Arg $arg, $index) use ($placeholders) {
                    return "['$placeholders[$index]', {$this->printer->prettyPrint([$arg])}]";
                }, $args, array_keys($args));

                // 第一行


                $newArgsCode = implode(',' . PHP_EOL, $newArgs);
                $newArgsCode = '$pre = [' . $newArgsCode . '];';
                $newArgs = $this->parser->parse("<?php \n" . $newArgsCode);

                array_splice($stmts, $index, 1, array_merge([$node], $newArgs));
            }, $stmts);

            if ($rootNode) {
                return $stmts;
            }

            return $oNode;
        }

        /**
         * @param $node
         *
         * @return bool
         */
        private function isSqlAssignVariable($node): bool
        {
            return $node instanceof Node\Stmt\Expression
                && $node->expr instanceof Node\Expr\Assign
                && $node->expr->var instanceof Node\Expr\Variable
                && $node->expr->var->name === 'sql'
                && $node->expr->expr instanceof Node\Expr\FuncCall
                && $node->expr->expr->name->getFirst() === 'sprintf'
                && preg_match(
                    '!^(update|select|insert|delete)\s!i',
                    trim($node->expr->expr->args[0]->value->value, ' ')
                );
        }

    }
);
$ast = $traverser->traverse($ast);
echo $prettyPrinter->prettyPrintFile($ast);

// dump($ast);
// $dumper = new NodeDumper;
// echo $dumper->dump($ast) . "\n";

处理后的结果

<?php

function foo()
{
    $sql = 'select * from t where name= ?  and age =  ?';
    $pre = [['s', $name], ['i', 1]];
    $sql = 'select * from t where name2= ?';
    $pre = [['s', $name]];
}
$sql = 'select * from t where name= ?  and age =  ?';
$pre = [['s', $name], ['i', 'xx']];
$sql = 'select * from t where name2= ?';
$pre = [['s', $name]];

---

@风兮清扬 提到的使用 quote 看起来也不错，不过查了一下，貌似也有一些缺陷，可以试试。

<?php

use PhpParser\Node;
use PhpParser\NodeTraverser;
use PhpParser\NodeVisitorAbstract;
use PhpParser\Parser;
use PhpParser\ParserFactory;
use PhpParser\PrettyPrinter\Standard;

require __DIR__ . '/vendor/autoload.php';

// 要解析的代码
$code = <<<'PHP'
<?php
    function foo(){
        $sql = sprintf("select * from t where name='%s' and age = '%d'",$name,1);
        $sql = sprintf("select * from t where name2='%s'",$name);
    }

    $sql = sprintf("select * from t where name='%s' and age = '%d'",$name,'xx');
    $sql = sprintf("select * from t where name2='%s'",$name);
PHP;


$parser = (new ParserFactory)->create(ParserFactory::PREFER_PHP7);
try {
    $ast = $parser->parse($code);
} catch (Error $error) {
    echo "Parse error: {$error->getMessage()}\n";
    return;
}

$prettyPrinter = new Standard;

$traverser = new NodeTraverser();

$traverser->addVisitor(
    new class($parser, $prettyPrinter) extends NodeVisitorAbstract {
        /**
         * @var Parser
         */
        private $parser;

        /**
         * @var Standard
         */
        private $printer;

        public function __construct($parser, $printer)
        {
            $this->parser = $parser;
            $this->printer = $printer;
        }

        public function leaveNode(Node $node)
        {
            if (!$this->isSqlAssignVariable($node)) {
                return;
            }
            $sql = trim($node->expr->expr->args[0]->value->value, ' ');
            $node->expr->expr->args[0]->value->value =
                trim(
                    preg_replace_callback(
                        $pattern = '/(([\'"]?)(?<!%)%(?:\.\d+)?[sdf]\2)/',
                        static function ($matches) {
                            return trim($matches[1], '"\'');
                        },
                        $sql
                    )
                    ,
                    ' '
                );


            $args = array_map(function (Node\Arg $arg) {
                return new Node\Arg(
                    new Node\Expr\MethodCall(
                        new Node\Expr\Variable('pdo'), new Node\Identifier('quote'), [$arg]
                    )
                );
            }, array_slice($node->expr->expr->args, 1));

            array_splice($node->expr->expr->args, 1, count($node->expr->expr->args), $args);
            return $node;
        }

        private function isSqlAssignVariable($node): bool
        {
            return $node instanceof Node\Stmt\Expression
                && $node->expr instanceof Node\Expr\Assign
                && $node->expr->var instanceof Node\Expr\Variable
                && $node->expr->var->name === 'sql'
                && $node->expr->expr instanceof Node\Expr\FuncCall
                && $node->expr->expr->name->getFirst() === 'sprintf'
                && preg_match(
                    '!^(update|select|insert|delete)\s!i',
                    trim($node->expr->expr->args[0]->value->value, ' ')
                );
        }
    }
);
$traverser->traverse($ast);
echo $prettyPrinter->prettyPrintFile($ast);

输出结果

<?php

function foo()
{
    $sql = sprintf("select * from t where name=%s and age = %d", $pdo->quote($name), $pdo->quote(1));
    $sql = sprintf("select * from t where name2=%s", $pdo->quote($name));
}
$sql = sprintf("select * from t where name=%s and age = %d", $pdo->quote($name), $pdo->quote('xx'));
$sql = sprintf("select * from t where name2=%s", $pdo->quote($name));