Dynamic update statement - prepared statement

Question

I am generating my MYSQL update statement dynamically in PHP. As I want my application to be secure to SQL injections I want to use the prepared statement function. But as I'm pretty experienced I'm struggling to do so. Below my code so far:

function sqlUpdate($tablename)
    {
        $connect = sqlConnect();
        $updateString = "UPDATE " . $tablename . " SET ";
        $columnname = getColumnname($tablename, false, true);
        
        for ($k=0; $k<count($columnname, COUNT_RECURSIVE); $k++)
        {
            if ($k+1 < count($columnname, COUNT_RECURSIVE))
            {
                $updateString .= $columnname[$k] . " = '" . mysqli_real_escape_string($connect, $_POST[$columnname[$k]]) . "', ";
            }
            else 
            {
                $updateString .= $columnname[$k] . " = '" . mysqli_real_escape_string($connect, $_POST[$columnname[$k]]) . "' WHERE " . $columnname[0] . " = '" . mysqli_real_escape_string($connect, $_POST[$columnname[0]]) . "';";
            }
        }
        
        if(mysqli_query($connect, $updateString))
        {
            echo "Daten wurden erfolgreich aktualisiert! </br>";
        }
        else 
        {
            echo "Es ist ein Fehler aufgetreten... </br>";
        }
        
        mysqli_close($connect); 
    }

My code is working fine at the moment but I'm not managing to get it to work with prepared statements. I hope my question is not too stupid. Can somebody share some thoughts how to realize it with my code or do I have to completly overthink my approach?

Sorry again for my noob question...

Thanks!

Have you read [a tutorial on prepared statements](https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection)? — KIKO Software, Oct 08 '20 at 15:35
Yes I did read several tutorials and it's also working on some other functions I use. My problem is only how to get it to work in this specific case when I'm dynamically generating my update statement depending on my tablename and user input. — Lucas, Oct 08 '20 at 15:40
You can't bind table or column names dynamically using prepared statements. So you should use another method to make sure these names are secure. For instance by checking against an array of valid values. However, you should still use a prepared statement to bind the values for those columns. — KIKO Software, Oct 08 '20 at 15:46
take allok at https://github.com/akilan/MYSQLI-INSERT-PREPARE-DYNAMIC/blob/master/mysqli-prepare-insert.php and https://stackoverflow.com/questions/5100046/how-to-bind-mysqli-bind-param-arguments-dynamically-in-php last uses https://www.php.net/manual/de/function.call-user-func-array.php for the bond_param. for that table names as long as you don't allow user input it is save to use conactoantion, if you have user input a whitelist should be made and only thos allowed — nbk, Oct 08 '20 at 16:05
Thanks a lot to all of you so far. I think the provided links will help me. I will come back to you to share my results once I have implemented the solution. — Lucas, Oct 09 '20 at 07:14

score 0 · Answer 1 · answered Oct 09 '20 at 09:06

Thanks to everybody who answered I managed to get it to work. I used the call_user_func_array function and can now generate the prepared statements for UPDATE and INSERT in one function:

function preparedStatement($tableName, $action)
    {
        $connect = sqlConnect();
        $stmt = $connect->stmt_init();
        $columnname = getColumnname($tableName, false, true);
        
        for ($k=0; $k<count($columnname, COUNT_RECURSIVE); $k++)
        {
            $fielddata[] = $columnname[$k];
            $fieldvalue[] = $_POST[$columnname[$k]];
        }
        if ($action == "insert")
        {
            $fieldvalue[0] = " ";
        }
        
        $fieldvalue_join = implode(',', array_map('addquote', $fieldvalue));
        $fieldvalue = explode(",",$fieldvalue_join);
        $valueCount = count($fieldvalue);
        $question_mark = array();
        
        for($i=0; $i<$valueCount; $i++)
        {
            $question_mark[] = '?';
        } 
        
        $join_question_mark = implode(",", $question_mark);
        $types = '';  
        
        foreach($fieldvalue as $param) 
        {        
            if(is_int($param)) 
            {
                $types .= 'i';              //integer
            } 
            elseif (is_float($param)) 
            {
                $types .= 'd';              //double
            } 
            elseif (is_string($param)) 
            {
                $types .= 's';              //string
            } 
            else 
            {
                $types .= 'b';              //blob and unknown
            }
        }
        
        if ($action == "insert")
        {
            $insertString = "INSERT INTO ".$tableName."(".implode(",",$fielddata).") VALUES (".$join_question_mark.");";
            $stmt->prepare($insertString);
            $bind_names[] = $types;
        }
        elseif ($action == "update")
        {
            $updateString = "UPDATE " . $tableName . " SET ";
            
            for ($k=0; $k<count($columnname, COUNT_RECURSIVE); $k++)
            {
                if ($k+1 < count($columnname, COUNT_RECURSIVE))
                {
                    $updateString .= $columnname[$k] . " = ?, ";
                }
                else 
                {
                    $updateString .= $columnname[$k] . " = ? WHERE " . $columnname[0] . " = '" . mysqli_real_escape_string($connect, $_POST[$columnname[0]]) . "';";
                }
            }
            
            $stmt->prepare($updateString);
            $bind_names[] = $types;
        }
        
        for ($i=0; $i<count($fieldvalue); $i++) 
        {
            $bind_name = 'bind' . $i;       
            $$bind_name = $fieldvalue[$i];
            $bind_names[] = &$$bind_name;   
        }
        
        call_user_func_array(array($stmt,'bind_param'),$bind_names);

        if($stmt->execute())
        { 
            $insert_id=$stmt->insert_id;
            $stmt->close();
            return $insert_id;
        }
        else
        {
            echo "Fehler beim Ausführen der Aktion...";
        }
    }
     
     function addquote($str)
     {
        if($str[0]=="'" || $str[0]=='"' && $str[strlen($str)-1]=="'" || $str[strlen($str)-1]=="'" )
        {
            $str=substr($str,1);
            $str=substr($str,0,-1);
        }
        return sprintf("%s", $str);
    }

Dynamic update statement - prepared statement

1 Answers1