Wie man MySQLi Prepared Statements mit Stored Procedures verwendet

Question

Ich versuche, mehr über MySQL zu erfahren und darüber, wie man sich gegen SQL-Injections schützen kann. Meine Nachforschungen haben mich zu Prepared Statements geführt, die der richtige Weg zu sein scheinen.

Ich arbeite auch daran, zu lernen, wie man Stored Procedures schreibt, und versuche jetzt, beides zu kombinieren. Dazu gibt es allerdings nicht viele Informationen.

Im Moment habe ich in meiner PHP-Testanwendung eine Funktion, die einen SP mit einem normalen MySQL-Befehl wie diesem aufruft:

mysql_query("CALL usp_inserturl('$longurl', '$short_url', '$source')");

Wie kann ich dasselbe mit MySQLi und einem Prepared Statement tun, um es so sicher wie möglich vor Injektionen zu machen?

Danke!

Answer 1

Versuchen Sie das Folgende:

$mysqli= new mysqli(... info ...); 
$query= "call YourSPWithParams(?,?,?)"; 
$stmt = $mysqli->prepare($query); 
$x = 1; $y = 10; $z = 14;
$stmt->bind_param("iii", $x, $y, $z); 
$stmt->execute();

Answer 2

Sie können beide gleichzeitig verwenden: bereiten Sie sie einfach mit einer gespeicherten Prozedur vor:

//prepare and bind SP's parameters with your variables only once
$stmt=$db->prepare("CALL MyStoredProc(?,?)");
$stmt->bind_param('is',$i,$name);

//then change binded variables and execute statement
for($i=1;$i<9;$i++)
{
  $name="Name".$i;
  $stmt->execute();
}

Denken Sie daran, dass Sie die Vorbereitung nur einmal durchführen sollten (nicht bei jeder Ausführung erneut), und führen Sie sie dann mehrmals aus (ändern Sie vorher nur den Parameterwert).

Answer 3

Das war ein bisschen schwierig, aber ich habe schließlich herausgefunden, wie ich eine gespeicherte Prozedur (mit IN-Parametern) verwenden kann, die eine vorbereitete Anweisung verwendet und die Daten über PHP abruft. Dieses Beispiel verwendet PHP 7.4.6 und MySQL 8.0.21 Community Edition.

Hier ist die Stored Procedure:

CREATE DEFINER=`root`@`loalhost` PROCEDURE `SP_ADMIN_SEARCH_PLEDGORS`(
    IN P_email VARCHAR(60),
    IN P_password_hash VARCHAR(255),
    IN P_filter_field VARCHAR(80),
    IN P_filter_value VARCHAR(255)
)
BEGIN
    #Takes admin credentials (first tow paramaters and searches the pledgors_table where field name (P_filter_field) is LIKE value (%P_filter_value%))
    DECLARE V_admin_id INT(11);
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT;
        SELECT 'ERROR' AS STATUS, CONCAT('MySQL ERROR: ', @ERRNO, ': ', @MESSAGE_TEXT) AS MESSAGE;
    END;
    SELECT admin_id INTO V_admin_id FROM admin_table WHERE password_hash = P_password_hash AND email = P_email;
    IF ISNULL(V_admin_id) = 0 THEN    
        SET @statement = CONCAT('SELECT pledgor_id, email, address, post_code, phone, alt_phone, contact_name
        FROM pledgors_table
        WHERE ',P_filter_field, ' LIKE \'%', P_filter_value, '%\';');
        PREPARE stmnt FROM @statement;
        EXECUTE stmnt;
    ELSE
        SELECT 'ERROR' AS STATUS, 'Bad admin credentials' AS MESSAGE;
    END IF;
END

Und hier ist das PHP-Skript

query = 'CALL SP_ADMIN_SEARCH_PLEDGORS(\''.
strtolower($email).'\', \''.
$password_hash.'\', \''.
$filter_field.'\', \''.
$filter_value.'\');';

$errNo = 0;
//$myLink is a mysqli connection
if(mysqli_query($myLink, $query)) {
    do {
        if($result = mysqli_store_result($myLink)) {
            while($row = mysqli_fetch_assoc($result)) {
                $data[] = $row;
            }
            mysqli_free_result($result);
        }
    } while(mysqli_next_result($myLink));
}
else {
    $errNo = mysqli_errno($myLink);
}
mysqli_close($myLink);

Answer 4

Die folgende Antwort könnte für Sie von Nutzen sein:

MySql: Wird die Verwendung von Prepared Statements zum Aufrufen einer gespeicherten Prozedur mit .NET/Connector schneller sein?

Darüber hinaus:

Gewähren Sie nur Ausführungsrechte, damit Ihre Benutzer der Anwendungsebene nur gespeicherte Prozeduren aufrufen können. Auf diese Weise kann Ihr(e) Anwendungsbenutzer nur über Ihre Stored-Procedure-API mit der Datenbank interagieren, nicht aber direkt mit ihr:

select, insert, delete, update, truncate, drop, describe, show etc. 

Sicherer geht's nicht mehr. Die einzige Ausnahme ist, wenn Sie dynamisches Sql in Ihren gespeicherten Prozeduren verwendet haben, was ich um jeden Preis vermeiden würde - oder sich zumindest der Gefahren bewusst sein, wenn Sie dies tun.

Bei der Erstellung einer Datenbank, z. B. foo_db, lege ich normalerweise zwei Benutzer an. Der erste foo_dbo (Datenbankeigentümer) ist der Benutzer, dem die Datenbank gehört und der volle Berechtigungen (ALL) erhält, so dass er Schemaobjekte erstellen und Daten nach Belieben manipulieren kann. Der zweite Benutzer foo_usr (Anwendungsbenutzer) hat nur Ausführungsrechte und wird in meinem Anwendungscode verwendet, um über die von mir erstellte Stored Procedure API auf die Datenbank zuzugreifen.

grant all on foo_db.* to foo_dbo@localhost identified by 'pass';

grant execute on foo_db.* to foo_usr@localhost identified by 'pass';

Schließlich können Sie Ihr obiges Codebeispiel verbessern, indem Sie mysql_real_escape_string verwenden:

• http://php.net/manual/en/function.mysql-real-escape-string.php

   $sqlCmd = sprintf("call usp_inserturl('%s','%s','%s')", 
    mysql_real_escape_string($longurl),  
    mysql_real_escape_string($shorturl), 
    mysql_real_escape_string($source));
  
   $result = mysql_query($sqlCmd);