PHP sql Injection and custom numbers of parameters in function

Question

Good day everyone: I'd like to parametrize my queries, creating a function that receive my query, connection and array with parameters expressed as "?". My function is:

receiveQuery($query, $mysqli1, $array1)

I have read about sql injection I would like to know that if this is a proper way to avoid these. I am planning to use this this function for INSERT, DELETE, UPDATE and SELECT. Also I would like you to guide me how could I create some better handling for more than 1 parameter, because currently I am using a switch. But every time I require more parameters, I am increasing the switch and I would like to create it dinamically.

SWITCH ($array1Length)

Any comments is helpful, regards. Felipe

<?php
    $mysqli1 = openConn();
    $query = "INSERT INTO tblTest (field1 , field2 ) VALUES (?,?)";
    $array1 =                array($value1, $value2);
    $result = receiveQuery($query, $mysqli1, $array1);
    if($stmt->affected_rows == 1)
    {
        $success = "Success.";
    }
    if($stmt->affected_rows == -1)
    {
        $error = "Error.";
    }
    closeConn($stmt);
    closeConn($mysqli1);

    function openConn()
    {
        $mysqli1 = new mysqli('localhost', 'userTest', '123', 'dbTest');
        if ($mysqli1->connect_error) {
            die('Connect Error (' . $mysqli1->connect_errno . ') '
                    . $mysqli1->connect_error);
        }
        return $mysqli1;
    }

    function receiveQuery($query, $mysqli1, $array1)
    {
        global $stmt;
        $stmt = $mysqli1->prepare($query);
        if (false===$stmt)
        {
            echo $mysqli1->error;
            die('Error');
        }
        $array1Length = count($array1);
        SWITCH ($array1Length)
        {
            CASE   0: break;
            CASE   1: $stmt->bind_param("s"   , $array1[0])                                 ;break;
            CASE   2: $stmt->bind_param("ss"  , $array1[0],$array1[1])                      ;break;
            CASE   3: $stmt->bind_param("sss" , $array1[0],$array1[1],$array1[2])           ;break;
            CASE   4: $stmt->bind_param("ssss", $array1[0],$array1[1],$array1[2],$array1[3]);break;
            DEFAULT : echo "Error";
        }
        $stmt->execute();
        $result = $stmt->get_result();

        return $result;
    }

    function closeConn($mysqli1)
    {
        $mysqli1->close();
    }

?>

https://stackoverflow.com/questions/5100046/how-to-bind-mysqli-bind-param-arguments-dynamically-in-php/5108167#5108167 This answer in particular is very succinct with `str_repeat()`. Prior to php 5.6 we had to use call_user_func* but it is easier now with the `...` splat operator. — Michael Berkowski, Jun 04 '18 at 13:48
Just a nick pick but I'd suggest look into how to [create a class](http://php.net/manual/en/language.oop5.basic.php) so you can avoid using that ugly `global` call — IsThisJavascript, Jun 04 '18 at 13:50

score 2 · Answer 1 · answered Jun 04 '18 at 14:07

I'd like to parametrize my queries, creating a function that receive my query, connection and array with parameters expressed as "?"

My suggestion is that you rather use PDO than, the current mysqli that you using at the moment. PDO is easier to learn and can work easy with your current requirements.

Here's how you would do this with PDO.

page.php

<?php

define('DB_HOST', 'localhost');
define('DB_NAME', 'dbTest');
define('DB_USER', 'userTest');
define('DB_PASS', '123');
define('DB_CHAR', 'utf8');

class conn
{
    protected static $instance = null;

    protected function __construct() {}
    protected function __clone() {}

    public static function instance()
    {
        if (self::$instance === null)
        {
            $opt  = array(
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => FALSE,
            );
            $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
            self::$instance = new PDO($dsn, DB_USER, DB_PASS, $opt);
        }
        return self::$instance;
    }

    public static function __callStatic($method, $args)
    {
        return call_user_func_array(array(self::instance(), $method), $args);
    }

    public static function receiveQuery($sql, $args = [])
    {
        if (!$args)
        {
             return self::instance()->query($sql);
        }
        $stmt = self::instance()->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

anotherpage.php

<?php

require 'page.php';

$params = array($value1, $value2);
$sql = "INSERT INTO tblTest (field1 , field2 ) VALUES (?,?)";

$stmt  = conn::receiveQuery($sql, $params);

if($stmt->rowCount() > 0){

     $success = "Success.";
}else{

     $error = "Error.";
}
?>

To learn more about PDO you can follow this site : https://phpdelusions.net/pdo

score 2 · Accepted Answer · answered Jun 04 '18 at 14:21

2

You should be able to use the splat operator on your array.

$s = '';
for ($x = 0; $x < count($params); $x ++) {
    $s .= 's';
}

$stmt->bind_param($s, ...$params);

https://secure.php.net/manual/en/migration56.new-features.php

answered Jun 04 '18 at 14:21

delboy1978uk

12,118
2
21
39

One could also use `$s = str_repeat('s', count($params));` instead of your first four lines of code. – Bill Karwin Jun 04 '18 at 17:24
nice tip Bill! :-) – delboy1978uk Jun 05 '18 at 14:21
I am having a "Parse error: syntax error, unexpected '.'" in line "$stmt->bind_param($s, ...$params);" only when is on hosting. When is local is OK, have you got any ideas? – felipe Sep 10 '18 at 16:31
I know now, splat operator is for PHP 5.6 onwards, my hosting has 5.4. – felipe Sep 10 '18 at 16:43

PHP sql Injection and custom numbers of parameters in function

2 Answers2