Pdo INSERT/SELECT/UPDATE/DELETE

[ycpc55]

Hi,
i was wondering if my code below is the right way of doing it with PDO?I've been updating my site using pdo but just wanted to make sure im doing it right before i go ahead thanks.

//////////////////////////SELECT * FROM/////////////////////////////////
$sth = $db->prepare("SELECT * FROM users WHERE user = :user Or mail = :mail");
$query_params = array(
':user' => $_POST['user'],
':mail' => $mail
);
$sth->execute($query_params);

//////////////////////////INSERT INTO/////////////////////////////////
$sth = $db->prepare("INSERT INTO users (user,pass,salt,mail
) VALUES (:user,:pass,:salt,:mail)");
$salt = dechex(mt_rand(0, 2147483647)) . dechex(mt_rand(0, 2147483647));
$pass = hash('sha256', $_POST['pass'] . $salt);
$query_params = array(
':user' => $_POST['user'],
':pass' => $pass,
':salt' => $salt,
':mail' => $_POST['mail']
);
$sth->execute($query_params);

//////////////////////////UPDATE/////////////////////////////////
$sth = $db->prepare("UPDATE users SET user = :user WHERE mail = :mail");
$query_params = array(
':user' => $_POST['user'],
':mail' => $mail
);
$sth->execute($query_params);

//////////////////////////DELETE FROM/////////////////////////////////
$sth = $db->prepare("DELETE FROM users WHERE user = :user");
$query_params = array(
':user' => $_POST['user']
);
$sth->execute($query_params); 

[evenstar7139]

I am not very experienced with PDO but...

You might want to check that what is entered in $_POST['user'] is actually a username. I'm assuming the value comes from a text field or something. Anybody could type anything into it. Ditto for anything else coming from $_POST.

Also, is there a reason you're using SELECT *? Do you always need to select every single column? If you don't then I think it's better to just list the columns you need (e.g. SELECT firstName, lastName, dateofBirth). I think doing this makes your database hog less of the CPU...or something, I don't know. I just know everybody tells me to do it :P I used to have the database from hell because it ate up all the ram and CPU on my server and loved to crash all the time. It wouldn't have been so bad if I'd known more about database optimization back then.

I'm not a noob to PHP but I am to answering people's questions. Hopefully I was of some use. Helping other people is a good way to learn about PHP so I want to do it more.

[NogDog]

One thing to think about is how you're going to capture/trap errors. One approach with PDO is to use the PDO::setAttribute() method to specify that errors be handled as exceptions:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

Now, instead of doing "== false" tests or such on each prepare() and execute(), you can just wrap your DB operations (or even your whole script) in a try/catch block:

try {
  // lots of amazing code here
}
catch(PDOException $e) {
  error_log(print_r($e, true));
  // now do whatever you want when a DB action fails
}
catch(Exception $e) {
  // do whatever you want when anything else throws an exception
} 

[ycpc55]

Hi,
thanks for the reply's guys, it helped a lot.

[evenstar7139]

You're welcome. I try

[mistin.ca]

I am not very experienced with PDO but...

You might want to check that what is entered in $_POST['user'] is actually a username. I'm assuming the value comes from a text field or something. Anybody could type anything into it. Ditto for anything else coming from $_POST.

Also, is there a reason you're using SELECT *? Do you always need to select every single column? If you don't then I think it's better to just list the columns you need (e.g. SELECT firstName, lastName, dateofBirth). I think doing this makes your database hog less of the CPU...or something, I don't know. I just know everybody tells me to do it :P I used to have the database from hell because it ate up all the ram and CPU on my server and loved to crash all the time. It wouldn't have been so bad if I'd known more about database optimization back then.

I'm not a noob to PHP but I am to answering people's questions. Hopefully I was of some use. Helping other people is a good way to learn about PHP so I want to do it more.

That's one of the major benefits of pdo prepared statements/parameter binding.. It escapes it, so you can directly put $_POST in your execute as it's escaped prior, you still need to sanitize for XSS though.

Aslong as you never put ANY user input variable in the ->prepare() portion and only in the execute you're fine against 1snd and 2nd order sql injection..

You should still have your dilligence in XSS sanitizing though. <script language="javascript"> alert('teehee'); </script> kind of thing.

$query = $db->prepare("INSERT INTO news SET name = ?, body = ?, date = ?");
$query->execute( array($_POST["name"], $_POST["body"], time() ) );