CODEWITHSUNDEEP
phpmysqlpdoinsert
DDDD
DDDD

Reputation: 3940

PHP sql using PDO

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

Upvotes: 1

Views: 98

Answers (4)

Ravi Dhoriya ツ
Ravi Dhoriya ツ

Reputation: 4414

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.
    }
}

Upvotes: 1

Pedro Koblitz
Pedro Koblitz

Reputation: 21

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.

Upvotes: 1

Fabio
Fabio

Reputation: 23490

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();
}

Upvotes: 2

echolocation
echolocation

Reputation: 1120

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.

Upvotes: 1

Related Questions