Is my PDO query safe from SQL injection

Question

I'm fairly new to PDO and wondering if my query below is safe from SQL injection. I'll be using this method throughout the site if so.

    // make connection to DB
$db = new PDO('mysql:host='.$dateBaseHost.';dbname='.$dateBaseName, $dateBaseUsername, $dateBasePassword);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


//simple query and binding with results
$query = $db->prepare(" SELECT * FROM `profile` WHERE `fullname` = :fullname ");

$search = (isset($_GET['search']) === true) ? $_GET['search'] : '' ; // ? : shorthand for if else

// bind parameters - avoids SQL injection
$query->bindValue(':fullname', $search);

//try... if not catch exception
try {
    // run the query
    $query->execute();

    $rows = $query->fetchAll(PDO::FETCH_ASSOC);
    echo '<pre>', print_r($rows, true),'</pre>';
}
catch (PDOException $e){
    sendErrorMail($e->getMessage(), $e->getFile(), $e->getLine());
}

As long as your are using parametrized,queries Your query is safe. — Rohitink, Mar 18 '13 at 16:36
Yes. You're using parameterisation, which will do the necessary escaping automatically. Great stuff! — halfer, Mar 18 '13 at 16:36

score 8 · Answer 1 · answered Mar 18 '13 at 16:36

8

Yes - parameterized queries are safe from injection when used in this way.

answered Mar 18 '13 at 16:36

Martin

6,632
4
25
28

Its just safe against Basic attacks... – Yousha Aleayoub Feb 04 '17 at 11:41

score 5 · Answer 2 · answered Mar 18 '13 at 16:38

As long as you use prepared statements properly, you're safe from injection. but as soon as you DIRECTLY insert any external data into a query, even if it's otherwise a prepared statement, e.g.

INSERT INTO $table VALUES (:param)

you're vulnerable - $table can be subverted in this case, even though you're using a prepared statement.

Anyone who tells you simply switching mysql->PDO or mysqli will make you safer is a flat out WRONG. You can be just as vulnerable to injection attacks with either library.

score 3 · Answer 3 · answered Mar 18 '13 at 16:43

3

You should also

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

By default it uses emulated mode, which merely does what mysql_real_escape_string does. In some edge cases, you're still vulnerable to SQL injection.

answered Mar 18 '13 at 16:43

Marcel Korpel

21,536
6
60
80

@YourCommonSense: but still important, see the dupe's answers, especially [this one](http://stackoverflow.com/a/12202218/258127). – Marcel Korpel Mar 18 '13 at 16:57
what does this actually do? – user2183216 Mar 18 '13 at 17:07
@user2183216: that's in my answer already, it turns off emulated mode. In emulated mode, PDO merely puts quotes around the variable and escapes all single quotes, but that might not be enough. Also read the duplicate question. – Marcel Korpel Mar 18 '13 at 17:14

Your Common Sense · Answer 4 · 2013-03-18T16:55:58.780

yes, it's fairly safe but whole script could be improved:

if (isset($_GET['search']) {
    // make connection to DB
    $opt = array(
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );
    $dsn = "mysql:host=$dateBaseHost;dbname=$dateBaseName;charset=$dateBaseCharset";
    $db  = new PDO($dsn, $dateBaseUsername, $dateBasePassword, $opt);

    //simple query and binding with results
    $query = $db->prepare("SELECT * FROM profile WHERE fullname = ?");
    $query->execute(array($_GET['search']));
    $rows = $query->fetchAll();
    echo '<pre>', print_r($rows, true),'</pre>';
}

you need to set errmode as a connection option
never use try..catch to handle error message. if you want to have a email on every error (which is just crazy), you have to set up my_exception handler() for this.
setting search to empty string doesn't make any sense
connect to PDO should be moved so separate file (not shown)
charset have to be set in DSN

why should you **never** use a `try...catch` block to handle errors? they have it in all of the documentation..? — gloomy.penguin, Nov 07 '13 at 01:18
I naver said "error" but "error message". Yes, they have it all wrong — Your Common Sense, Nov 07 '13 at 05:01
I would like to know your thoughts on Marc B's answer and solution . — Prafulla Kumar Sahu, Jul 20 '17 at 03:01

Is my PDO query safe from SQL injection

4 Answers4