douzhe9075
douzhe9075
2014-03-20 20:27

在SQLite3中避免SQL注入

已采纳

I'm trying to figure out a good easy way to avoid SQL Injection and so far I've only been able to come up with two ideas:

  • Base64 encode the user input (Don't really want to do this)
  • Use regex to remove unwanted characters. (Currently using this, not sure if it's 100% safe)

Here is my current code:

    <?php
        $hash = $_GET['file'];

        if (isset($hash))
        {
            $db = new SQLite3("Files.db");

            if ($db != null)
            {
                $hash = preg_replace('/[^A-Za-z0-9 _.\-+=]/', '_', $hash);

                if ($response = $db->query("SELECT [FILE] FROM '$hash'"))
                {
                    echo $response->fetchArray()[0]; // File name is returned if successful.
                }
            }
        }
    ?>

My question is am I going about this the right way or are there any better ways to do this?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dtoqemais553654797 dtoqemais553654797 7年前

    Get your database library to do it for you. The PHP Sqlite3 library supports prepared statements:

    $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
    $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
    
    点赞 评论 复制链接分享
  • douyi1779 douyi1779 7年前

    The common advice to protect against SQL injection is to use prepared queries and use parameters to combine dynamic content into your SQL expressions.

    But this only works if you use the dynamic content in place of a single scalar value, like a number or a quoted string or date literal.

    In your case, you show a PHP variable replacing a table name in the FROM clause. You can't use a parameter for this usage. In fact, you show a usage that won't work in any case, because you're quoting the table name with single-quotes, as if it's a string literal. I don't think SQLite supports this.

    To help protect against SQL injection for a dynamic table name, the best practice is to use whitelisting. That is, compare the input against a list of known valid table names, and if the user tries to enter anything that's not in that list, reject it.

    <?php
    
    $valid_tables = array('table1', 'table2', 'table3');
    
    $hash = $_GET['file'];
    
    if (array_search($hash, $valid_tables)) {
        ...do your query...
    } else {
        ...issue an error or else some default behavior...
    }
    

    You may create the $valid_tables array by hard-coding it, reading a config file, or you may query your SQLite database for all the currently existing tables.

    点赞 评论 复制链接分享

相关推荐