PHP sql using PDO

Question

I am trying to learn how to use PDO so my forms aren't SQL injected. I am confused on how to code the execute function for my insert.

This is my code:

$db = new PDO('mysql:host=x;dbname=x;charset=utf8', 'x', 'x');
if ( !$db )
{
die('Could not connect: ' . mysql_error());
}
$ipaddress = $_SERVER['REMOTE_ADDR'];
$mail = $_POST['mail'];

$stmt = $db->prepare("SELECT * FROM ucm_signup WHERE email =? ") or exit(mysql_error());
$stmt->bindValue(1, $mail, PDO::PARAM_STR);
$stmt->execute();
$num_rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if($num_rows == 0) 
{ 
//if there are no duplicates...insert

$sql = $db->prepare("INSERT INTO ucm_signup company, address1, address2, city, province, zip, fname, lname, email, phone, session, iama, buyfrom, group1, ipaddress )
VALUES ( '$_POST[company]','$_POST[address1]','$_POST[address2]','$_POST[city]', '$_POST[province]','$_POST[zip]','$_POST[fname]','$_POST[lname]','$_POST[mail]', '$_POST[phone]','$_POST[session]','$_POST[iama]','$_POST[buyfrom]','$_POST[group1]', '$ipaddress')");
$sql->execute(array(':company' => I AM LOST))
}

Am I even close on the INSERT ? Thank you

Nope, you aren't. Why don't you use the same prepared statement principle you used for SELECT? — Your Common Sense
– Your Common Sense, Commented Mar 20, 2014 at 18:24
Put your $_POST[fields] in the execute(array(...)), associate them to :placeholder names, which you put in the INSERT statement in place of the quoted literal variables. — mario
– mario, Commented Mar 20, 2014 at 18:24
If you want to learn PDO, read the tutorials and follow them to a T. Here, read this — Funk Forty Niner
– Funk Forty Niner, Commented Mar 20, 2014 at 18:26
VALUES(:foo) then execute(array(":foo" => "bar")) where bar is $_POST['bar'] in your case.. — ponciste
– ponciste, Commented Mar 20, 2014 at 18:26
$field1 is to be read between the lines. You need to do $field1=$_POST['company']; as an example. You don't do the INSERT that way for your VALUES. @DDDD — Funk Forty Niner
– Funk Forty Niner, Commented Mar 20, 2014 at 18:30

Fabio · Accepted Answer · 2014-03-20 18:32:13Z

2

Instead of fetching rows and checking against zero you should check for rowCount(). Your first query is correct, but the second one needs to be changed, you will need to use prepared statment here as well and binding your parameter, you could use for example

$num_rows = $stmt->rowCount();
if($num_rows == 0) 
{ 
    $sql = $db->prepare("INSERT INTO ucm_signup company, address1, address2, city, province, zip, fname, lname, email, phone, session, iama, buyfrom, group1, ipaddress )
    VALUES (:company,:address1,:address2, .....)");
    $sql->bindParam(':company',$_POST[company],PDO::PARAM_STR);
    $sql->bindParam(':address1',$_POST[address1],PDO::PARAM_STR);
    $sql->bindParam(':address2',$_POST[address2],PDO::PARAM_STR);
    //and so on
    $sql->execute();
}

answered Mar 20, 2014 at 18:32

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

echolocation · Accepted Answer · 2014-03-20 18:31:33Z

No! Do not do this! always bind your parameters. By directly placing $_POST['company'] in, someone could inject SQL code by submitting it through post. Here's a better solution:

$sql = $db->prepare("INSERT INTO ucm_signup company, address1, address2, city, province, zip, fname, lname, email, phone, session, iama, buyfrom, group1, ipaddress
VALUES ( :company, :address1, :address2,:city, :province, :zip, :fname, :lname, :mail, :phone, :session, :iama, :buyfrom, :group1, :ipaddress)");
$sql->bindValue(":company", $_POST['company']);
$sql->bindValue(":address1", $_POST['address1']);
//...
$sql->bindValue(":ipaddress", $ipaddress);
$sql->execute()
}

The reason being, PDO sends the prepared statement and parameters separately. By doing this, it automatically prevents against SQL injection. A user could put ANYTHING into those variables, and it still would not inject SQL because of how PDO utilizes parameterized statements.

Pedro Koblitz · Accepted Answer · 2014-03-20 18:37:39Z

You are doing it right with the SELECT query. Why arent you doing the same with the INSERT?

I did not debug your code, so it may not be just copy and paste:

// first of all count($num_rows)!
if(count($num_rows) == 0) 
{ 
    // you forgot "()" in column names and ":" in the binds On column values
    $sql = $db->prepare("INSERT INTO ucm_signup (column, another_column) VALUES (:value1, :value2)");
    $sql->execute(
        array(
            "value1" => $_POST['value1'],
            "value2" => $_POST['value2'],
        )
    );
}

Other than that is worth noticing a couple of things: PDO is like a first step. It doesnt do a lot of things that sophisticated database layers do. Even though you're preventing injection you should not use $_POST directly in the bind. It still lacks validation. If you're learning, it is a good start. But for something that would resemble code for production I recommend you take a look at some abstraction layer like Doctrine DBAL. I am not talking about the ORM. The DBAL is a thin layer on top of PDO.

Ravi Dhoriya ツ · Accepted Answer · 2014-03-20 18:40:45Z

This one would be more readable and easy to understand.

$ipaddress = $_SERVER ['REMOTE_ADDR'];
$email = $_POST ['mail'];
$stmt = $db->prepare ( "SELECT * FROM ucm_signup WHERE email =:email " );
$stmt->bindParam(":email", $mail);
$stmt->execute ();

if ($stmt->rowCount()==0) {
    // if there are no duplicates...insert

    $sql = $db->prepare ( "INSERT INTO ucm_signup(company, address1, address2, city, province, zip, fname, lname, email, phone, session, iama, buyfrom, group1, ipaddress)
        VALUES (:company,:address1,:address2,:city,:province,:zip,:fname,:lname,:email,:phone,:session,:iama,:buyfrom,:group1,:ipaddress)" );
    $sql->bindParam(":company", $_POST['company']);
    $sql->bindParam(":address1", $_POST['address1']);
    $sql->bindParam(":city", $_POST['city']);
    $sql->bindParam(":province", $_POST['province']);
    $sql->bindParam(":zip", $_POST['zip']);
    $sql->bindParam(":fname", $_POST['fname']);
    $sql->bindParam(":lname", $_POST['lname']);
    $sql->bindParam(":email", $_POST['email']);
    $sql->bindParam(":phone", $_POST['phone']);
    $sql->bindParam(":session", $_POST['session']);
    $sql->bindParam(":imea", $_POST['imea']);
    $sql->bindParam(":buyfrom", $_POST['buyfrom']);
    $sql->bindParam(":imea", $_POST['imea']);
    $sql->bindParam(":group1", $_POST['group1']);
    $sql->bindParam(":ipaddress", $_POST['ipaddress']);

    $sql->execute ();
    if($sql->rowCount()==1){
        //successfully inserted
    }else{
        //oops, some error occured.
    }
}

Collectives™ on Stack Overflow

PHP sql using PDO

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related