JeffCoderr JeffCoderr - 1 year ago 77
SQL Question

MySQL Prepared statement confusion

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.


Here is how I validate API Key:

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

And like mentioned above this can easily be bypassed by executing my API like this'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?

Answer Source

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){
    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){
    $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;
        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 = ?");
    $stmt->bind_param("s", $key);
    $results = Database::execute($stmt);
    return count($results > 0);

PHP automatically closes DB connection so no worries about it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download