When to use prepared statements?

Question

I have few question.

Spec: MySql database; server side language PHP 5.3.10

1) When should one use prepared statements?

I am building a webapp which has users. I am retrieving/inserting data into the database constantly. I am currently not using prepared statements and I was wondering if that was wrong way of doing things?

/**
     * Register a new user into the database.  Please make sure to
     * hash the password.
     * @param type $fName First name of the user - String value.
     * @param type $lName Last name of the user - String value.
     * @param type $email Email address of the user - String value.
     * @param type $hashedPassword - String value.
     * @return boolean true if sucessful or false if failed.
     */
    function registerUser($fName, $lName, $email, $hashedPassword)
    {
        //Establish a connection.
        $mysqli = new $mysqli($GLOBALS['dbServer'], $GLOBALS['dbUserName'], $GLOBALS['dbPassword'], $GLOBALS['dbName']);

        //Check if connection failed.
        if($mysqli->connect_error)
        {
            die('Connect Error (' .$mysqli->connect_errno . ') '
                    .$mysqli->connect_error);
        }

        //Insert data into the table and check if the attempt was sucessful.
        if($mysqli->query("INSERT INTO user_info(email, password, fName, lName) VALUE  ('$email', '$hashedPassword', '$fName', '$lName')"))
        {
            return true;
        }
        return false;            
    }

Is this the proper way to insert a value into the database and make sure that it is successful? Alternatively, I can use prepared statements and I was wondering

2) how would I go about using prepared statements? And why should I (if you suggest that I do)?

I'm expecting roughly 20,000 visits to the website on daily bases. Or lets assume that's how many...

No offence, but this question ("why should I") has been asked thousands times already. Why not to search a little first? [for exapmle](http://stackoverflow.com/a/8265319/285587) — Your Common Sense, Jan 26 '13 at 08:53

score 0 · Accepted Answer · answered Jan 26 '13 at 08:51

0

You should always used prepared statements. That would prevent any chance of SQL injection (provided preparation done right). I'm guessing you also want to know when you can use regular queries for efficiency; hardware can always be upgraded. Watch out for second order SQL injection (example).

answered Jan 26 '13 at 08:51

Raekye

5,081
8
49
74

Thanks for that link. I read the article and currently I was doing exactly that. I had a function to prevent XSS and SQL injections but as the article pointed out that's not the best way to do it. – PAujla03 Jan 26 '13 at 21:38

score 0 · Answer 2 · edited May 23 '17 at 12:27

Apart from the question "Why should I", which has been answered already, there are some things in yur code that needs be corrected.

creating new database connection in the every function is not the way to go.
Create it once somewhere in bootstrap file, and then use global keyword to access it.
Dying in the middle of the code is another no-no.
Stuffing many operators into single line does no good but makes code hard to read and support.
Unlike PDO, Mysqli offers no help with reducing repetitons in the code, so, a programmer have to care.

so, this function is going to be like

function registerUser($fName, $lName, $email, $hashedPassword)
{
    global $mysqli;

    //Insert data into the table and check if the attempt was sucessful.
    $sql = "INSERT INTO user_info(email, password, fName, lName) VALUES (?,?,?,?)";
    $sth = $mysqli->prepare($sql);
    foreach (func_get_args() as $i => $value) {
        $sth->bindValue($i+1, $value, PDO::PARAM_STR);
    }
    $mysqli->execute();
    return !$mysqli->error;
}

I'm not familiar with managing connections. Do you have any recommended tutorials? I'll do search on the web on my own too but was wondering if you had any suggestions. In your example above when/where is that connection closed? Wouldn't I end up with lots of connections if I don't open it and close it in the function that is being used? — PAujla03, Jan 26 '13 at 21:33

When to use prepared statements?

2 Answers2

Linked