www.webdeveloper.com
Results 1 to 6 of 6

Thread: Pdo INSERT/SELECT/UPDATE/DELETE

  1. #1
    Join Date
    Dec 2006
    Posts
    15

    Pdo INSERT/SELECT/UPDATE/DELETE

    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.
    PHP Code:
    //////////////////////////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(02147483647)) . dechex(mt_rand(02147483647));
    $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); 

  2. #2
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    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.
    Last edited by evenstar7139; 08-17-2012 at 04:53 PM.
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,158
    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:
    PHP Code:
    $db->setAttribute(PDO::ATTR_ERRMODEPDO::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:
    PHP Code:
    try {
      
    // lots of amazing code here
    }
    catch(
    PDOException $e) {
      
    error_log(print_r($etrue));
      
    // now do whatever you want when a DB action fails
    }
    catch(
    Exception $e) {
      
    // do whatever you want when anything else throws an exception

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Dec 2006
    Posts
    15
    Hi,
    thanks for the reply's guys, it helped a lot.

  5. #5
    Join Date
    Jun 2006
    Location
    Under your bed
    Posts
    357
    You're welcome. I try
    The better I get at programming, the more I appreciate arrays. Handy dandy things they are.

  6. #6
    Join Date
    Jul 2012
    Location
    Vancouver
    Posts
    61
    Quote Originally Posted by evenstar7139 View Post
    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.

    PHP Code:
    $query $db->prepare("INSERT INTO news SET name = ?, body = ?, date = ?");
    $query->execute( array($_POST["name"], $_POST["body"], time() ) ); 
    Last edited by mistin.ca; 08-18-2012 at 03:20 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles