Using PDO to avoid SQL injections - newbie

Question

I'm trying to use PDO to avoid sql injections and have been looking and searching around for examples and this is what I've come up with, but there are some kind of error somewhere. The database is not getting updated and I get and sql error, but it wont print the details.

elseif (isset($_POST["bilnr"])) {

        $name = $_POST['name']; $mobil = $_POST['mobil']; $bilnr = $_POST['bilnr']; $regnr = $_POST['regnr']; $userid = $_COOKIE[userid]; $username = $_COOKIE[user]; 

        $sql=$oDB->Prepare("UPDATE members SET name=:name, mobil=:mobil, bilnr=:bilnr, regnr=:regnr WHERE id=:userid AND username=:username");
        $sql->execute(array(':userid' => $userid);

    if (!$sql) {
        echo "\nPDO::errorInfo():\n";
        print_r($oDB->errorInfo());
    }   

    echo "<p class=\"red\">Informasjonen er oppdatert!</p>";
    mysqli_close($con); }

If or when I remove the mysqli_close string something crashes and the page just turns blank with no errors. Also with the code above the updates being made in the form dont get into the database.

and the PDO connection in a separate file which is being included

$oDB=new PDO("mysql:host=$host;dbname=$db_name", $username, $password);

Here is the updated code

elseif (isset($_POST["bilnr"])) {

        $name = $_POST['name']; $mobil = $_POST['mobil']; $bilnr = $_POST['bilnr']; $regnr = $_POST['regnr']; $userid = $_COOKIE[userid]; $username = $_COOKIE[user]; 

        $sql=$oDB->Prepare("UPDATE members SET name=:name, mobil=:mobil, bilnr=:bilnr, regnr=:regnr WHERE id=:userid AND username=:username");
        $sql->execute(array(':userid' => $userid,
                    ':name' => $name,
                    ':mobile' => $mobile,
                    ':bilnr' => $billnr,
                    ':regnr' => $regnr,
                    ':username' => $username));

        if (!$sql) {
            echo "\nPDO::errorInfo():\n";
            print_r($oDB->errorInfo());
        }       

    echo "<p class=\"red\">Update Done!</p>";
    mysqli_close($con); }

The next problem is to get the values into the database, as it is now I don't receive any errors so I'm not sure whats wrong.

UPDATE

It works, was just some typo's in the array variables :)

You can't use `mysqli_query` and `mysqli_error` in a PDO program. — Barmar, Apr 17 '14 at 09:18
It looks like you are mixing up mysqli and pdo - you can't use PDO object in mysqli_* functions. — Mikk, Apr 17 '14 at 09:18
duplicate of http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858 — Your Common Sense, Apr 17 '14 at 09:18
hehe yeah I used the mysql stuff first, then decided to convert to a PDO solution cause I was made aware of the fact that my script was wide open for sql injections :) — Roger Andersen, Apr 17 '14 at 09:25
why you are constantly keeping mysqli functions in your code? Can't you use search feature in your editor, eliminate them ALL, then run your code, see if it works and only then post it here? — Your Common Sense, Apr 17 '14 at 09:40

score 0 · Answer 1 · answered Apr 17 '14 at 09:18

0

The first argument for mysqli_query must be a string (representation of an SQL query) but you are passing it a PDO prepared query.

Don't mix and match multiple database libraries.

Use PDO or mysqli_.

answered Apr 17 '14 at 09:18

Quentin

914,110
126
1,211
1,335

Im supposed to use PDO now :) – Roger Andersen Apr 17 '14 at 09:26

score 0 · Accepted Answer · answered Apr 17 '14 at 09:21

0

First, you can't mix mysqli and PDO. Second, the problem with your query is that you have 6 placeholders, but you're only filling in one of them when you call execute(). It should be:

$sql->execute(array(':userid' => $userid,
                    ':name' => $name,
                    ':mobile' => $mobile,
                    ':bilnr' => $billnr,
                    ':regnr' => $regnr,
                    ':username' => $username));

answered Apr 17 '14 at 09:21

Barmar

741,623
53
500
612

Updated the code now, but I still don't get the new form values into the db – Roger Andersen Apr 17 '14 at 09:33
What happened to the incorrect code in the question? – Barmar Apr 17 '14 at 09:37
If you want to show updated code, add it as an **UPDATE** at the end of the question, don't remove the error from the original code. It makes my answer look like nonsense. – Barmar Apr 17 '14 at 09:43

Wcool · Answer 3 · 2014-04-17T09:40:21.347

-1

You don't need mysqli_* functions anymore, scrap 'em (only when you're using PDO) :) When you're using PDO, mysql_* and mysqli_* don't work when combining them. I recommend you to use PDO and not mysql functions anymore. PDO is now well established and is the preferred way.

$sql = " 
    INSERT INTO table (name) 
    VALUES (:name) 
    "; 

//Here you prepare the SQL (you already did that correctly).
$stmt = $db->prepare($sql); 

//You can choose to use bindParam, bindValue or include it in the array (as you do it).
$stmt->bindParam(':name', $name, PDO::PARAM_STR); 

$name = 'John'; 
$stmt->execute();

This is an example how to insert something into a MySQL database with PDO.

edited Apr 17 '14 at 09:40

answered Apr 17 '14 at 09:19

Wcool

329
1
2
9

Could anybody explain the downvote? Would love to hear feedback – Wcool Apr 17 '14 at 09:25
This answer is of very poor quality and hardly could be salvaged. strictly speaking, there is nothing wrong with mysqli_* functions and the OP may scrap PDO functions in favor of them as well. Example you posted is too generalized and helps nothing with the OP – Your Common Sense Apr 17 '14 at 09:36
@YourCommonSense: Feedback taken. I might have answered a bit too quickly indeed. Sorry, won't happen again :) Did an edit too. – Wcool Apr 17 '14 at 09:38

Using PDO to avoid SQL injections - newbie

3 Answers3