Customize MySql query with PDO

Question

I'm making a function to query my database, fully, with a keyword search ($wordsToSearch) or with some category tags words($tagsToSearch) if there are.
This is my function, and it's not secure since i use the concat to add some part of the query. How should I use PDO to filter the variabiles and then add the part of the query when it is necessary?
Thanks to everybody

$wordsToSearch = " ";
$tagsToSearch = " ";

if(is_string($search)){
    $wordsToSearch = "WHERE (
                            `artist_nm` LIKE  '%".$search."%'
                            OR  `place` LIKE  '%".$search."%'
                            )";
}
if(is_string($searchtags)){
    $arrayTags = explode(',', $searchtags);
    $tagsToSearch = "HAVING (
                            `tags` LIKE  '%".$arrayTags[0]."%' ";
    foreach ($arrayTags as $key => $value) {
        if($key != 0 && $key <= 20)  {
            $tagsToSearch .= "OR `tags` LIKE  '%".$value."%' ";
        }
    }
    $tagsToSearch .= ")";

}

$database->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$STH = $database->prepare('SELECT id, lat, lng, CONCAT_WS(  "/&/", total, tags ) AS data
    FROM (SELECT lat, lng, id, CONCAT_WS(  "/&/", img_link, artist_nm, page_link, place, Total_Rating, Rating_Number ) AS total, GROUP_CONCAT( tag_name
    SEPARATOR  "," ) AS tags
    FROM images
    LEFT OUTER JOIN tbl_places ON images.id = tbl_places.KE_img
    LEFT OUTER JOIN rel_tags ON images.id = rel_tags.Id_immagine
    LEFT OUTER JOIN tags ON tags.Id_tag = rel_tags.Id_tag
    '.$wordsToSearch.'
    GROUP BY id '.$tagsToSearch.'
    ) AS subquery
    '); 
try {
    $STH->execute();
} catch(PDOException $e){
    echo $e->getMessage();
    die();
}

Happy · Answer 1 · 2014-01-29T10:12:53.893

You are looking for prepared requests. You have to put compile your query with some parameters with prepare() method:

<?php

// With placeholders
$sth = $database->prepare('SELECT * FROM table WHERE id = ?'); 

// With named parameters
$sth = $database->prepare('SELECT * FROM table WHERE id = :id');

?>

Then you can execute the query using execute() method:

<?php

// With placeholders
$sth->bindParam(1, $yourId, PDO::PARAM_INT);
$sth->execute();
// or
$sth->execute(array($yourId));

// With named parameters
$sth->bindParam(':id', $yourId, PDO::PARAM_INT);
$sth->execute();
// or
$sth->execute(array(':id' => $yourId));

?>

Edit:

Of course you can put more than one parameter:

<?php

// With placeholders
$sth = $database->prepare('SELECT * FROM table WHERE username = ? AND password = ?');
$sth->bindParam(1, $username, PDO::PARAM_STR);
$sth->bindParam(2, $password, PDO::PARAM_STR);
$sth->execute();
// or
$sth->execute(array($username, $password));


// With named parameters
$sth = $database->prepare('SELECT * FROM table WHERE username = :username AND password = :password');
$sth->bindParam(':username', $username, PDO::PARAM_STR);
$sth->bindParam(':password', $password, PDO::PARAM_STR);
$sth->execute();
// or
$sth->execute(array(':username' => $username, ':password' => $password));

?>

Customize MySql query with PDO

1 Answers1