Prepared Statements

cottton · 4. Februar 2013

Ich dachte mir, da ich sowieso gerade den Testblock hier hab, kann ich ihn auch gleich hier posten.
Hab mir Gedanken gemacht, wie ich P.S. für mich am sinnvollsten nutze. Und falls jemand Ideen hat - her damit =)

PHP

<?php







// settings:
define('MYSQL_HOST',      'localhost');
define('MYSQL_BENUTZER',  'xxx'); // <-
define('MYSQL_KENNWORT',  '');
define('MYSQL_DATENBANK', 'xxx'); // <-
define('TABLE','xxx'); // <-




define('ERROR_MESSAGE_SQL','Ooops ...');
$main_array['timing']['SQL']['time'] = 0;
$main_array['timing']['SQL']['count'] = 0;




// connect to the db
try{
    $main_array['sql']['dbh'] = new PDO('mysql:host='.MYSQL_HOST.';dbname='.MYSQL_DATENBANK, MYSQL_BENUTZER, MYSQL_KENNWORT, array(PDO::ATTR_PERSISTENT => true));
    define('SQL_CONNECTED_AND_SELECTED',true);
    prepareSQLStatements($main_array);
}catch(PDOException $e){
    $main_array['error']['admin'][] = 'Error: '.$e->getMessage();
    $main_array['error']['user'][] = ERROR_MESSAGE_SQL;
    define('SQL_CONNECTED_AND_SELECTED',false);
}




// prepare statements
function prepareSQLStatements($main_array)
{
    global $main_array;




    $main_array['sql']['stmts']['sel_id_where_nick'] = $main_array['sql']['dbh']->prepare("SELECT id FROM ".TABLE." WHERE nickname = :nickname");
    //$main_array['sql']['stmts']['sel_id_where_nick'] = $main_array['sql']['dbh']->prepare("SELECT id FROM ".TABLE." WHERE nickname = :nickname and test = :test");
    $main_array['sql']['stmts']['sel_nick_where_id'] = $main_array['sql']['dbh']->prepare("SELECT nickname FROM ".TABLE." WHERE id = :id");
}




// execute statements plus catch errors, timing, amount of queries
function executeStatement($main_array, $stmt, $array)
{
    global $main_array;




    if($array === false){
        $time_startSQL = microtime(true);
        if(!$stmt->execute()){
            $arr = $stmt->errorInfo();
            $main_array['error']['admin'][] = 'SQL Error: (SQLSTATE error code: '.$arr[0].') '.$arr[2];
            $main_array['error']['user'][] = ERROR_MESSAGE_SQL;




            $main_array['timing']['SQL']['time'] += microtime(true) - $time_startSQL;
            $main_array['timing']['SQL']['count'] += 1;
            return false;
        }else{
            $main_array['timing']['SQL']['time'] += microtime(true) - $time_startSQL;
            $main_array['timing']['SQL']['count'] += 1;
            return($stmt);
        }
    }else{
        $time_startSQL = microtime(true);
        /*debug*/echo "<pre>";print_r($stmt);print_r($array);echo"</pre><hr />";
        if(!$stmt->execute($array)){
            $arr = $stmt->errorInfo();
            $main_array['error']['admin'][] = 'SQL Error: (SQLSTATE error code: '.$arr[0].') '.$arr[2];
            $main_array['error']['user'][] = ERROR_MESSAGE_SQL;




            $main_array['timing']['SQL']['time'] += microtime(true) - $time_startSQL;
            $main_array['timing']['SQL']['count'] += 1;
            return false;
        }else{
            $main_array['timing']['SQL']['time'] += microtime(true) - $time_startSQL;
            $main_array['timing']['SQL']['count'] += 1;
            return($stmt);
        }
    }
}







// do some ...
if(SQL_CONNECTED_AND_SELECTED === true){
    if(!$main_array['sql']['stmts']['sel_nick_where_id'] = executeStatement($main_array, $main_array['sql']['stmts']['sel_nick_where_id'], array('id' => 1))){
        // do something if failed
    }else{
        if($row = $main_array['sql']['stmts']['sel_nick_where_id']->fetch(PDO::FETCH_ASSOC)){
            $nickname = $row['nickname'];
        }
    }




    if(!$main_array['sql']['stmts']['sel_id_where_nick'] = executeStatement($main_array, $main_array['sql']['stmts']['sel_id_where_nick'], array('nickname' => 'cottton'))){
        // do something if failed
    }else{
        if($row = $main_array['sql']['stmts']['sel_id_where_nick']->fetch(PDO::FETCH_ASSOC)){
            $id = $row['id'];
        }
    }
}







// output
echo 'Result nickname: '.@$nickname.'<br />';
echo 'Result id: '.@$id.'<br />';
echo '<hr />';
echo 'SQL time: '.$main_array['timing']['SQL']['time'].'<br />';
echo 'SQL queries: '.$main_array['timing']['SQL']['count'].'<br />';
echo '<hr />';
echo 'errors:'.'<br />';
$x = 0;
if(isset($main_array['error']['admin'])){
    foreach($main_array['error']['admin'] as $err){
        $x++;
        echo '#'.$x.'(adm) '.$err.'<br />';
    }
}
$x = 0;
if(isset($main_array['error']['user'])){
    echo '<br />';
    foreach($main_array['error']['user'] as $err){
        $x++;
        echo '#'.$x.'(user) '.$err.'<br />';
    }
}
if($x == 0){
    echo 'No errors';
}







?>

Alles anzeigen

Ich glaube ich fange alle Fehler ab.
Nur wenn man mehr vordefiniert als man "reinschickt" gibts komischerweise keine e-message, sondern nur den e-code.
(bsp "...WHERE nickname = :nickname and test = :test" wobei "test" später nicht reingeschickt wird. Ergibt dann nur e-code: 'HY093')

cottton · 19. Februar 2013

Falls mal jemand Prepared S. mit LIMIT verwenden möchte:

PHP

$stmt = $dbh->prepare("SELECT * FROM tablename WHERE name = :name LIMIT :start, :rows"); 
$stmt->execute( array('name' => 'cottton', 'start' => 0, 'rows' => 50) );

Wird nicht funktionieren da die Werte als String übergeben(?) werden =)
Also endet die Query als:

SQL

SELECT * FROM tablename WHERE name = ´cottton´ LIMIT ´0´,´50´

Lösung: PDO::PARAM_INT

PHP

$stmt->bindParam(':param', $var, PDO::PARAM_INT);

Sieht dann so aus:

PHP

$stmt = $dbh->prepare("SELECT * FROM tablename WHERE name = :name LIMIT :start, :rows"); 
$stmt->bindParam(':name', $name, PDO::PARAM_STR); // String
$stmt->bindParam(':start', $start, PDO::PARAM_INT); // int
$stmt->bindParam(':rows', $rows, PDO::PARAM_INT); // int




$name = 'cottton';
$start = 0;
$rows = 50;




$stmt->execute();

Alles anzeigen

Wer man das Ganze dynamisch möchte:

PHP

$stmt = $dbh->prepare("SELECT * FROM tablename WHERE name = :name LIMIT :start, :rows"); 
$bindpara['name'] = 'cottton';  
$bindpara['start'] = 0;
$bindpara['rows'] = 50;




foreach($bindpara as $key => &$val){ // &$val um eine Referenz zum Wert zu bekommen
    if(is_string($val)){
        $stmt->bindParam($key, $val, PDO::PARAM_STR); // String
    }elseif(is_int($val)){
        $stmt->bindParam($key, $val, PDO::PARAM_INT); // int
    }elseif(is_bool($val)){
        echo 'error: val is bool';
    }
}
$stmt->execute();

Alles anzeigen

PS: es müssen dann wohl alle parameter auf die gleiche Art und Weise (bindParam) übergeben werden.
Hab einen "Mix" mit bindParam und execute( array('name' => 'username') ) getestet und rannte in Fehlermeldungen.

unkown-6363 · 24. Februar 2013

Wie der Zufall so will habe ich mir heute auch Prepared Statemants angeschaut, und das auch ohne Beeinflussung durch diesen Thread und anschließend bin ich bei diesem Thread gelandet. :'D
Ich habe das ganze noch nicht so richtig verstanden (Sinn, Nutzen, ...), aber das könnte was ganz sinnvolles sein. :'D

cottton · 24. Februar 2013

Zum Sinn: Das Beste und sicher jedem wichtigste ist, dass sql injection nicht mehr möglich ist, WENN man es so nutzt, wie es vorgesehen ist.

Bsp wie man es "versaut":

PHP

$stmt = $dbh->prepare("SELECT * FROM tablename WHERE name = ".$_GET['name']." LIMIT $_POST['user_input'], :rows");

Somit würde man die query mit user daten füllen.
Also immer :name ect nutzen. Dann ist es "egal", was man beim execute(array('nickname' => $_GET['user_input'])) reinschickt.

angenommen: $_GET['name'] = " ';cottton "
dann wird hier ein bissl was schief laufen:

PHP

$stmt = $dbh->prepare("SELECT * FROM players WHERE nickname = '".$_GET['name']."' LIMIT 0,1"); 
$stmt->execute();

query: SELECT * FROM players WHERE nickname = ' ';cottton ' LIMIT 0,1

richtig wäre:

PHP

$stmt = $dbh->prepare("SELECT * FROM players WHERE nickname = :nickname LIMIT 0,1"); 
$stmt->execute(
 array(
 'nickname' => $_GET['name']
)
);

query: ja, wie stellt man das dar?
Ich hab noch kein Bsp gesehen, wie die query dann aussieht.
Aber die query ist jedenfalls schon "fertig" - prepared.
Kann also "nicht mehr geändert werden". Die Daten werden als Parameter übergeben.
(Weiß nicht wie ich es sonst ausdrücken soll ;D )

unkown-6363 · 24. Februar 2013

Die Werte werden sozusagen von der Query gekappselt.?

cottton · 24. Februar 2013

So klingt es besser, ja ;D

cottton · 30. Mai 2013

Info: Falls jemand auch schon mal eine Query mit "WHERE IN (a,b,c,d)" via Prepared Statements nutzen wollte
und in google keinerlei Sinvolle Lösung gefunden hat -> hier ist eine funktionierende (mMn sinnvolle) Lösung:

PHP

# angenommenes input-array (zB checkbox[] inputfeld )
$checkboxes = array(
    0 => 'alles mögliche',
    1 => 'usw',
    2 => 'blub',
    3 => 'value',
);




if($checkboxes){




    $paras['deleted'] = time(); # key und val sind klar, also rein in $paras
    foreach($checkboxes as $key => $msg_id){ # wir wissen nicht wieviele/welche offsets das array hat - also foreach()
        if(!isset($placeholder)){
            $placeholder = ':'.($x='a'); # $placeholder erstellen (wir benötigen ja ein format wie zB :a)
        }else{
            $placeholder .= ',:'.(++$x); # anhängen und $x hoch zählen (in etwa :a,:b,:c)
        }
        $paras[$x] = $msg_id; # $paras füllen mit dem key $x (zB :a) und dem wert
    }




    if(isset($paras)){
        $stmt = $dbh->prepare("UPDATE ".MYSQL_DATABASE.".tablelle SET deleted = :deleted WHERE msg_id IN (".$placeholder.");");
        $stmt->execute($paras);
    }




}




# output $placeholder:
#   :a,:b,:c,:d




# output sql query:
#   UPDATE MYSQL_DATABASE.tablelle SET deleted = :deleted WHERE msg_id IN (:a,:b,:c,:d);




# output $paras:
/*
Array
    (
        [deleted] => 1369868305
        [a] => alles mögliche
        [b] => usw
        [code=c] => blub
        [d] => value
    )
*/

Alles anzeigen

Da kann user reinschicken was er will. Es wird sauber und ordentlich via Prepared Statements an die db übergeben =)

Prepared Statements

Jetzt mitmachen!

Teilen