Should I use bindValue() or execute(array()) to avoid SQL injection?

Question

As a prevention against SQL injections, I'm using PDO. I have seen people using both the methods ie: bindValue() and then execute() or just execute(array())

Do both the methods prevent the attack? Since mysql_real_escape_string() is deprecated is there anything else I should consider using here?

Like for $aenrollmentno should I typecast into

$aenrollmentno = (int)($_POST['aenrollmentno']);

Will this be safe enough if I'm not using it in a prepared statement? Any other security measure that I'm missing?

   <?php  


     if(isset($_POST['aenrollmentno']))
     {
    $aenrollmentno = mysql_real_escape_string($_POST['aenrollmentno']); 
     }



 if(isset($_POST['afirstname']))
        {
            $afirst_name  = mysql_real_escape_string($_POST['afirstname']);
            $afirstname = ucfirst(strtolower($afirst_name));

    }




    //PDO connection     
    try {


        $conn = new PDO('mysql:host=localhost;dbname=practice','root','');
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

        $stmt = $conn->prepare('INSERT INTO  modaltable(afirstname, alastname,aenrollmentno) VALUES (:afirstname,:alastname,:aenrollmentno)');

        $stmt->execute(array(

        'afirstname' => $afirstname,
        'alastname' => $alastname,
        'aenrollmentno' => $aenrollmentno,

        ));



    echo "Success!";


    }
    catch (PDOException $e) {
        echo 'ERROR: '. $e->getMessage();
    }


    ?>

Funny you should ask. [**Have a read at this...**](http://stackoverflow.com/q/5741187/1415724) Read the whole thing, you stand at being quite surprised; I was. — Funk Forty Niner, Dec 12 '13 at 18:34
mysql_real_escape_string isn't deprecrated in and of itself. the **ENTIRE** mysql_*() function set is deprecated. As well, m_r_e_s() requires an active connection to the server for it to operate, which you're unlikely to have, since you're using PDO. you canNOT mix/match the libraries like that. PDO prepared statements almost ENTIRELY remove the need to manually escape data anyways. — Marc B, Dec 12 '13 at 18:39
since you're using prepared statements, the m_r_e_s call would only serve to essentially double-escape data and trash it in ways you're not going to like later on. — Marc B, Dec 12 '13 at 18:40

score 1 · Accepted Answer · answered Dec 12 '13 at 18:32

1

execute(array) is just a shortcut for a loop that calls bindValue on each of the array elements. Use whatever suits your program flow best. Both prevent SQL injection.

Rule of thumb: Whatever you pass to prepare should NOT, in any way, depend on user input. You can pass anything you want to execute() - you might get runtime errors, e.g. if you try to put a non-numeric string into a number column - but you won't allow SQL injections.

answered Dec 12 '13 at 18:32

Guntram Blohm

9,667
2
24
31

Typecasting might still help against the "database error when trying to insert a string into a numeric column" case, but it won't have any effect on SQL injection, if you apply it to bound values (execute(array) is just a special case of bound values). – Guntram Blohm Dec 12 '13 at 18:39

Should I use bindValue() or execute(array()) to avoid SQL injection?

1 Answers1

Linked