phpmysqlpdosql-injection

Protection query against SQL injection, using PDO


may I ask you how to make this protected against sql injection?

I have

$podminkazeme = "";
$podminkakraj = "";

if ( isset( $_GET[ "zeme" ] )and !empty( $_GET[ "zeme" ] ) ) {
   $podminkazeme = "and nb.zemehledani in (" . str_replace( '%2C', ',',  $_GET[ "zeme" ] ) . ")";
};

if ( isset( $_GET[ "kraj" ] )and !empty( $_GET[ "kraj" ] ) ) {
  $podminkakraj = "and nb.krajhledani in (" . str_replace( '%2C', ',', $_GET[ "kraj" ]  ) . ")";
};

$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sqljednotkaa = "
  select nb.*, 
    dv.nazev as developer, 
    UNIX_TIMESTAMP(nb.datumAktualizace) as datumAktualizace, 
    UNIX_TIMESTAMP(nb.datumPripomenuti) as datumPripomenuti, 
    uz.nazev as skladUpravil, 
    uzm.nazev as makler, 
    tp.nazev as typProdeje, 
    dal.nazev as nazevdalnice
  from nabidka nb 
  left join uzivatele uz on uz.id=nb.skladUpravil 
  left join uzivatele uzm on uzm.id=nb.makler 
  left join typProdeje tp on tp.id=nb.typProdeje 
  left join developer dv on dv.id=nb.developer
  left join dalnice dal on dal.id=nb.dalnice 
  where nb.emptyid is null 
    $podminkaid 
    $podminkastav 
    $podminkaupraveno 
    $podminkavelikost 
    $podminkacena 
    $podminkamakler 
    $podminkaexport 
    $podminkatypprodeje 
    $podminkatypnemovitosti 
    $podminkazeme 
    $podminkadalnice 
    $podminkakraj 
    $podminkaokres 
    $podminkadeveloper 
    $podminkatechnickeparametry 
    $podminkahledanislovo 
  order by nb.emptyid asc $razenipodminka";

$stjednotkaa = $conn->prepare( $sqljednotkaa );
$stjednotkaa->execute();

Solution

  • Try this

    $conditions = [];
    $parameters = [];
    
    if (isset($_GET["zeme"]) && !empty($_GET["zeme"])) {
        $zemeValues = explode(',', $_GET["zeme"]);
        $conditions[] = "nb.zemehledani IN (" . implode(', ', array_fill(0, count($zemeValues), '?')) . ")";
        $parameters = array_merge($parameters, $zemeValues);
    }
    
    if (isset($_GET["kraj"]) && !empty($_GET["kraj"])) {
        $krajValues = explode(',', $_GET["kraj"]);
        $conditions[] = "nb.krajhledani IN (" . implode(', ', array_fill(0, count($krajValues), '?')) . ")";
        $parameters = array_merge($parameters, $krajValues);
    }
    
    $conn = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);
    $sqljednotkaa = "SELECT nb.*, dv.nazev as developer, UNIX_TIMESTAMP(nb.datumAktualizace) as datumAktualizace, UNIX_TIMESTAMP(nb.datumPripomenuti) as datumPripomenuti, uz.nazev as skladUpravil, uzm.nazev as makler, tp.nazev as typProdeje, dal.nazev as nazevdalnice from nabidka nb left join uzivatele uz on uz.id=nb.skladUpravil left join uzivatele uzm on uzm.id=nb.makler left join typProdeje tp on tp.id=nb.typProdeje left join developer dv on dv.id=nb.developer left join dalnice dal on dal.id=nb.dalnice where nb.emptyid is null";
    if ($conditions) {
        $sqljednotkaa .= " AND " . implode(" AND ", $conditions);
    }
    $sqljednotkaa .= " ORDER BY nb.emptyid ASC $razenipodminka";
    $stjednotkaa = $conn->prepare($sqljednotkaa);
    $stjednotkaa->execute($parameters);