MySQL Prepared statement confusion

Question

Ok, so I am having a lot of trouble with Prepared statements. I've done hours of research and still can't seem to fully understand everything...

I really feel like I need to understand Prepared statements because I was just about to release a few new free APIs on my website (which require API Key to execute API) but I recently realized how insecure everything is.... I can simply use SQL injection to bypass API Key check, e.g. 'OR'1'='1

Here is how I validate API Key:

$apikey = $_GET['key'];
$sql = "SELECT * FROM `table` WHERE `key` = '$apikey'";
$query = mysqli_query($con, $sql);
if($query)
{
    $fetchrow = mysqli_fetch_row($query);
    if(isset($fetchrow[0]))
    {
        echo "API Key is valid!";
    }
    else
    {
        echo "API KEY is invalid";
    }
}

And like mentioned above this can easily be bypassed by executing my API like this

http://website.com/api.php?key='OR'1'='1

This really scared me at first, but then I did some research and learned a good way to prevent any form of SQL injection is to use prepared statement, so I did a lot of research and it just seems quite complicated to me :/

So I guess my question is, how can I take my above code, and make it function the same way using prepared statements?

The security does not come from prepared statements but from use of parameter binding. See http://bobby-tables.com/php.html for example code. — Steffen Ullrich, Aug 28 '16 at 05:24
Possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Alexander O'Mara, Aug 28 '16 at 05:27
@AlexanderO'Mara lol...... Maybe read someone's question before saying It's a possible duplicate.................. — JeffCoderr, Aug 28 '16 at 05:37
I did read it, that's how I know you should find your answer over there. — Alexander O'Mara, Aug 28 '16 at 05:38
They can run any SQL command using nested queries, so bypassing this api key security is really only the tip of your security concern iceberg. The basic idea is to never use string concatenation to build an SQL query--ever. The `$` escape facility in PHP is a stealthy string concatenation. — le3th4x0rbot, Aug 28 '16 at 06:30

DamianoPantani · Accepted Answer · 2017-05-04T15:01:45.763

Probably everything you need:

class Database {
    private static $mysqli;

Connect to the DB:

public static function connect(){
    if (isset(self::$mysqli)){
        return self::$mysqli;
    }
    self::$mysqli = new mysqli("DB_HOST", "DB_USER", "DB_PASS", "DB_NAME");
    if (mysqli_connect_errno()) {
        /*Log error here, return 500 code (db connection error) or something... Details in $mysqli->error*/
    }
    self::$mysqli->query("SET NAMES utf8");
    return self::$mysqli;
}

Execute statement and get results:

public static function execute($stmt){
    $stmt->execute();
    if ($mysqli->error) {
        /*Log it or throw 500 code (sql error)*/
    }
    return self::getResults($stmt);
}

Bind results to the pure array:

private static function getResults($stmt){
    $stmt->store_result();
    $meta = $stmt->result_metadata();

    if (is_object($meta)){
        $variables = array();
        $data = array();

        while($field = $meta->fetch_field()) {
            $variables[] = &$data[$field->name];
        }

        call_user_func_array(array($stmt, "bind_result"), $variables);

        $i = 0;
        while($stmt->fetch()) {
            $array[$i] = array();
            foreach($data as $k=>$v)
            $array[$i][$k] = $v;
            $i++;
        }
        $stmt->close();
        return $array;
    } else {
        return $meta;
    }
}

Class end :)

Example of usage:

public function getSomething($something, $somethingOther){
    $mysqli = Database::connect();
    $stmt = $mysqli->prepare("SELECT * FROM table WHERE something = ? AND somethingOther = ?");
    $stmt->bind_param("si", $something, $somethingOther); // s means string, i means number
    $resultsArray = Database::execute($stmt);
    $someData = $resultsArray[0]["someColumn"];
}

Resolving your problem:

public function isKeyValid($key){
    $mysqli = Database::connect();
    $stmt = $mysqli->prepare("SELECT * FROM table WHERE key = ? LIMIT 1");
    $stmt->bind_param("s", $key);
    $results = Database::execute($stmt);
    return count($results > 0);
}

PHP automatically closes DB connection so no worries about it.

score 1 · Answer 2 · answered Aug 28 '16 at 06:00

$sql = "SELECT * FROM `table` WHERE `key` = ?";

if(stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("i", $apikey);

    $stmt->execute();
    $stmt->bind_result($res);
    $stmt->fetch();

    $stmt->close();
}

See more - http://php.net/manual/en/mysqli.prepare.php

MySQL Prepared statement confusion

2 Answers2