1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

PDO Question

Discussion in 'PHP' started by Jeremy Benson, Nov 8, 2013.

  1. #1
    Hey,

    I'm working with the PDO object for the first time. I'm not sure how to fire my queries, so that information is inserted, or selected, from the database. Hopefully someone can help me sort out the code :)

    PHP:
    1. if($numberOfErrors == 0){
    2.        
    3.             $db = new PDO($dsn, $dbUserName, $dbPassword);
    4.            
    5.             $dbEntryUserName = $_POST['userName'];
    6.             $dbEntryPassword = $_POST['password'];
    7.             $dbEntryEmail = $_POST['email'];
    8.             $dbEntrySecQuestion = $_POST['securityQuestion'];
    9.             $dbEntrySecAnswer = $_POST['securityAnswer'];
    10.                    
    11.             $userRegistrationQuery = "INSERT INTO `userinfo` (`userName`, 'userPassword', 'userEmail', 'userSecurityQuestion', 'userSecurityAnswer') VALUES ('$dbEntryUserName','$dbEntryPassword','$dbEntryEmail','$dbEntrySecQuestion','$dbEntrySecAnswer')";
    12.            
    13.             $sqlStatement = $db->prepare($userRegistrationQuery);      
    14.                
    15.             $db->execute($sqlStatement);  
    16.         }
    Jeremy Benson, Nov 8, 2013 IP
  2. nico_swd

    nico_swd Prominent Member

    Messages:
    4,119
    Likes Received:
    331
    Best Answers:
    17
    Trophy Points:
    325
    #2
    Replace the values with question marks. Like so:

    Code (Text):
    1.  
    2. VALUES (?, ?, ?, ...)
    3.  
    Then you pass the variables in an array to execute(), like this:
    PHP:
    1.  
    2. $sqlStatement->execute([$dbEntryUserName, $dbEntryPassword, $dbEntryEmail, ...]);
    3.  
    The first question mark will be replaced with the first item in the array, the second with the second item, etc...

    Note that you have to execute() the statement returned by $db->prepare(), and not the $db object.


    If you have a lot of variables in your query string, it can get confusing, so you instead of the question marks, you can also do this:
    Code (Text):
    1.  
    2. VALUES (:username, :userpassword, etc...)
    3.  
    PHP:
    1.  
    2. $sqlStatement->execute([':username' => $dbEntryUserName, ':userpassword' => $dbEntryPassword, ...]);
    3.  
    nico_swd, Nov 8, 2013 IP
  3. Jeremy Benson

    Jeremy Benson Member

    Messages:
    118
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #3
    I'll show you what I have here so far... I'm having a hard time understanding from the small bits of code...I'm in highly uncharted waters here, but would like to understand...

    PHP:
    1. if($numberOfErrors == 0){
    2.      
    3.             $db = new PDO($dsn, $dbUserName, $dbPassword);
    4.          
    5.             $dbEntryUserName = $_POST['userName'];
    6.             $dbEntryPassword = $_POST['password'];
    7.             $dbEntryEmail = $_POST['email'];
    8.             $dbEntrySecQuestion = $_POST['securityQuestion'];
    9.             $dbEntrySecAnswer = $_POST['securityAnswer'];
    10.                  
    11.             $userRegistrationQuery = "INSERT INTO `userinfo` (`userName`, 'userPassword', 'userEmail', 'userSecurityQuestion', 'userSecurityAnswer') VALUES ('$dbEntryUserName','$dbEntryPassword','$dbEntryEmail','$dbEntrySecQuestion','$dbEntrySecAnswer')";
    12.          
    13.             $db->execute([$dbEntryUserName,$dbEntryPassword,$dbEntryEmail,$dbEntrySecQuestion,$dbEntrySecAnswer]);
    14.          
    15.             $sqlStatement = $db->prepare($userRegistrationQuery);    
    16.              
    17.             $sqlStatement->execute([':username' => $dbEntryUserName, ':userpassword' => $dbEntryPassword =>, ':userEmail' => $dbEntryEmail, ':userSecurityQuestion' => $dbEntrySecQuestion, ':$dbEntrySecAnswer' => $dbEntrySecAnswer])
    18.         }
    19.      
    20.  
    I know this code isn't even close...I was trying to work in what you were telling me, but with short strings I couldn't understand... sorry.
    Jeremy Benson, Nov 8, 2013 IP
  4. MakZF

    MakZF Active Member

    Messages:
    390
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    70
    #4
    You're doing it wrong! The whole point of prepared statements is that you DON'T build up your query's statement with PHP variables but rather the query's variables are substituted in after the statement is compiled, vastly improving security.

    Here's a simple example of executing a statement (might be a few errors/typos in the code, but you get the gist of it):

    PHP:
    1. $db = new PDO($dsn, $dbUserName, $dbPassword);
    2.  
    3. $username = $_POST['username'];
    4. $email = $_POST['email'];
    5. $password = $_POST['password'];
    6.  
    7. $stmt = $db->prepare("INSERT INTO members(name, email, password) VALUES(:name, :email, :password)");
    8. $stmt->bindParam(':name', $name, PDO::PARAM_STR);
    9. $stmt->bindParam(':email', $email, PDO::PARAM_STR);
    10. $stmt->bindParam(':password', $password, PDO::PARAM_STR);
    11.  
    12. $stmt->execute();
    Alternatively:

    PHP:
    1. $db = new PDO($dsn, $dbUserName, $dbPassword);
    2.  
    3. $username = $_POST['username'];
    4. $email = $_POST['email'];
    5. $password = $_POST['password'];
    6.  
    7. $stmt = $db->prepare("INSERT INTO members(name, email, password) VALUES(?, ?, ?)");
    8. $stmt->bindParam(1, $name, PDO::PARAM_STR);
    9. $stmt->bindParam(2, $email, PDO::PARAM_STR);
    10. $stmt->bindParam(3, $password, PDO::PARAM_STR);
    11.  
    12. $stmt->execute();
    Last edited: Nov 8, 2013
    MakZF, Nov 8, 2013 IP
  5. nico_swd

    nico_swd Prominent Member

    Messages:
    4,119
    Likes Received:
    331
    Best Answers:
    17
    Trophy Points:
    325
    #5
    EDIT: A little too late...

    This is how it should be:
    PHP:
    1.  
    2. $sqlStatement = $db->prepare("
    3.    INSERT INTO `userinfo`
    4.        (`userName`, `userPassword`, `userEmail`, `userSecurityQuestion`, `userSecurityAnswer`)
    5.    VALUES
    6.        (?, ?, ?, ?, ?)
    7. ");
    8.  
    9. $sqlStatement->execute([$dbEntryUserName, $dbEntryPassword, $dbEntryEmail, $dbEntrySecQuestion, $dbEntrySecAnswer]);
    10.  
    $db->prepare() prepares a query string for secure execution. It makes sure that the data you provide in execute() goes just where the question marks are, and nowhere else. This is how it prevents SQL injections. It's called prepared statement.

    Does this make sense? The question marks will be replaced in the same order as the items in the execute() array. Don't ever put variables directly into the query string. It's very very insecure (unless you defined the variables, and not your users).
    nico_swd, Nov 8, 2013 IP
  6. Jeremy Benson

    Jeremy Benson Member

    Messages:
    118
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #6
    okay, this is starting to make sense now that I'm reading over your posts.

    The first thing to do is prepare a statement, like you have here...

    Code (Text):
    1. $sqlStatement = $db->prepare("
    2.   INSERT INTO `userinfo`
    3.       (`userName`, `userPassword`, `userEmail`, `userSecurityQuestion`, `userSecurityAnswer`)
    4.   VALUES
    5.       (?, ?, ?, ?, ?)
    6. ");
    or like Mak has here.

    Code (Text):
    1. $stmt = $db->prepare("INSERT INTO members(name, email, password) VALUES(:name, :email, :password)");
    Just a couple of questions here... what goes into the statement through the prepare function, does it create an object or some kind of array? Also in the versions of this line where there isn't question marks and values are marked with : followed by a name, are these names dependent on any variable defined? I'd say not eh? Kind of like giving values a label?

    PHP:
    1.  
    2. $sqlStatement->execute([$dbEntryUserName, $dbEntryPassword, $dbEntryEmail, $dbEntrySecQuestion, $dbEntrySecAnswer]);
    3.  
    I take it this sends a sort of self contained array through execute which interpolates the values into sql statement?

    I'm also guessing this bit of code does the same kind of interpolation?

    PHP:
    1.  
    2. $stmt->bindParam(1, $name, PDO::PARAM_STR);
    3. $stmt->bindParam(2, $email, PDO::PARAM_STR);
    4. $stmt->bindParam(3, $password, PDO::PARAM_STR);
    5.  
    One more thing...not sure where the security increase comes from when all the variables are still contained in the script.

    sorry, hope I'm starting to get this, lol... I'm also downloading the PHP manual at the moment.
    Jeremy Benson, Nov 8, 2013 IP
  7. MakZF

    MakZF Active Member

    Messages:
    390
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    70
    #7
    Because it protects against SQL injection. There is no possibility of building the query's statement dynamically.
    MakZF, Nov 8, 2013 IP
  8. Jeremy Benson

    Jeremy Benson Member

    Messages:
    118
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #8
    Awesome. One thing I see in the manual. It says "If your application does not catch the exception thrown from the PDO constructor, the default action taken by the zend engine is to terminate the script and display a back trace. This back trace will likely reveal the full database connection details, including the username and password."

    I guess all I have to to do is create the object in a try and catch block? Seems easy enough. I'm going to have to go through the manual. Just downloaded it and it's making things a lot clearer. Didn't know how much of a difference it would make, lol...

    anyway, I'm gonna go home, and try to work this out. Likely I'll be back if there's errors..

    thanks for you guys help, and Nico too.

    Lessons have been great :)
    Jeremy Benson, Nov 8, 2013 IP
  9. MakZF

    MakZF Active Member

    Messages:
    390
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    70
    #9
    MakZF, Nov 8, 2013 IP
  10. Jeremy Benson

    Jeremy Benson Member

    Messages:
    118
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #10
    Hey,

    Back after the weekend. Had tried working with the PDO object using Nico's method with some luck. The only issue I had was not being able to figure out how to traverse the output array. Then with Mak's method strange things went into the database, lol..

    Nico's method works perfect for me, but can't seem to find out how to display retrieved information. This is the method I used to get something from the database.
    PHP:
    1.  
    2. require('system/prime_system_data/sqlpassword.php');
    3.  
    4. $suppliedName = "Jeremy";
    5.  
    6. $retrievedName = "";
    7.  
    8. $dbCheck = new PDO($dsn, $dbUserName, $dbPassword);
    9.  
    10. $sqlStatement = $dbCheck->prepare("SELECT `userName` FROM `userinfo` WHERE `userName` = ?");
    11.  
    12. $sqlStatement->execute([$suppliedName]);
    13.  
    14. $retrievedName = $sqlStatement->fetchAll();
    15.  
    16. var_dump($retrievedName);
    17.  

    The var_dump looks like this.


    array (size=1)
    0 =>
    array (size=2)
    'userName' => string 'Jeremy' (length=6)
    0 => string 'Jeremy' (length=6)

    I've tried everything to traverse that array to get to the name hauled from the database, but just couldn't seem to do it.

    Tried echo $retrievedName[0] , $retrievedName['userName'], $retrievedName[0]['userName]'

    nothing worked.

    **

    After some tinkering I got Mak's method working, but can't seem to figure out why the information is going into the database wrong.

    in my database I get user name - :userName password - :userPassword
    PHP:
    1.  
    2. $sqlStmt = $db->prepare("INSERT INTO `userinfo` (`userName`, `userPassword`, `userEmail`, `userSecurityQuestion`, `userSecurityQuestionAnswer`) VALUES (':userName', ':userPassword', ':userEmail', ':userSecQuestion', ':userSecAnswer')");
    3.  
    4. $sqlStmt->bindParam(':userName',$dbEntryUserName, PDO::PARAM_STR);
    5.  
    6. $sqlStmt->bindParam(':userPassword',$dbEntryPassword, PDO::PARAM_STR);
    7.  
    8. $sqlStmt->bindParam(':userEmail',$dbEntryEmail, PDO::PARAM_STR);
    9.  
    10. $sqlStmt->bindParam(':userSecQuestion',$dbEntrySecQuestion, PDO::PARAM_STR);
    11.  
    12. $sqlStmt->bindParam(':userSecAnswer',$dbEntrySecAnswer, PDO::PARAM_STR)
    13.  
    14. $sqlStmt->execute(array($dbEntryUserName=>':userName',$dbEntryPassword=>':userPassword',$dbEntryEmail=>':userEmail',$dbEntrySecQuestion=>':userSecQuestion',$dbEntrySecAnswer=>':userSecAnswer'));
    15.  
    Would love to understand the method above. Is that method using JSON object? Learned a bit about that in a JavaScript book.
    Jeremy Benson, Nov 12, 2013 IP
  11. PoPSiCLe

    PoPSiCLe Well-Known Member

    Messages:
    1,225
    Likes Received:
    109
    Best Answers:
    51
    Trophy Points:
    160
    #11
    You need to remove the single quotes from the query itself. Ie ':username' to :username
    PoPSiCLe, Nov 12, 2013 IP
  12. nico_swd

    nico_swd Prominent Member

    Messages:
    4,119
    Likes Received:
    331
    Best Answers:
    17
    Trophy Points:
    325
    #12
    That, and you have to swap the array keys with its values.

    It should be
    PHP:
    1.  
    2. ':userName' => $dbEntryUserName
    3.  
    fetchAll(), as the name suggests, fetches all rows. If you only want the first, call fetch().

    PHP:
    1.  
    2. $retrievedName = $sqlStatement->fetch(PDO::FETCH_ASSOC);
    3.  
    4. echo $retrievedName['userName']; // *should work*
    5.  
    Last edited: Nov 12, 2013
    nico_swd, Nov 12, 2013 IP
  13. PoPSiCLe

    PoPSiCLe Well-Known Member

    Messages:
    1,225
    Likes Received:
    109
    Best Answers:
    51
    Trophy Points:
    160
    #13
    Frankly, since he've used bindParam, he doesn't need the array on execute at all - just do $sqlStmt->execute();
    PoPSiCLe, Nov 12, 2013 IP
    nico_swd likes this.
  14. Jeremy Benson

    Jeremy Benson Member

    Messages:
    118
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #14
    Hey, thanks. I'm gonna get both methods working for me, so I know what I'm doing in the future. That makes sense about fetchall(). I even read through the docs and tried to match things up, but documentation code has an uncanny way of not working for me, lol.. Not sure if it's typos, or just not getting it. Thanks for the help though, you guys have taught this stuff well, and without posters wouldn't make it anywhere.
    Jeremy Benson, Nov 13, 2013 IP
  15. Jeremy Benson

    Jeremy Benson Member

    Messages:
    118
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #15
    Hmm, for now I'm using Nico's first suggested method, with the question marks because it works like a charm. Still a little disappointed that I can't get the other method though, lol. I tried removing quotes from the query, as well as taking the array out of the execute function. Not sure what's wrong still.. And I tried reversing the keys in the execute function, but that didn't work either..

    PHP:
    1.  
    2. $db = new PDO($dsn, $dbUserName, $dbPassword);
    3.  
    4. $sqlStmt = $db->prepare("INSERT INTO `userinfo` (`userName`, `userPassword`, `userEmail`, `userSecurityQuestion`, `userSecurityQuestionAnswer`) VALUES (:userName, :userPassword, :userEmail, :userSecQuestion, :userSecAnswer)");
    5.  
    6. $sqlStmt->bindParam(':userName',$dbEntryUserName, PDO::PARAM_STR);
    7.  
    8. $sqlStmt->bindParam(':userPassword',$dbEntryPassword, PDO::PARAM_STR);
    9.  
    10. $sqlStmt->bindParam(':userEmail',$dbEntryEmail, PDO::PARAM_STR);
    11.  
    12. $sqlStmt->bindParam(':userSecQuestion',$dbEntrySecQuestion, PDO::PARAM_STR);
    13.  
    14. $sqlStmt->bindParam(':userSecAnswer',$dbEntrySecAnswer, PDO::PARAM_STR);
    15.  
    16. $sqlStmt->execute();
    17.  
    Jeremy Benson, Nov 14, 2013 IP
  16. nico_swd

    nico_swd Prominent Member

    Messages:
    4,119
    Likes Received:
    331
    Best Answers:
    17
    Trophy Points:
    325
    #16
    I don't see anything wrong with it. Have you checked $sqlStmt->errorInfo() to see what failed?

    Alternatively, you can use this syntax to insert data, which is a little more readable and a little less confusing.
    PHP:
    1.  
    2. $sqlStmt = $db->prepare("
    3.    INSERT INTO `userinfo`
    4.    SET
    5.        `userName` = :userName,
    6.        `userPassword` = :userPassword,
    7.        `userEmail` = :userEmail,
    8.        `userSecurityQuestion` = :userSecAnswer,
    9.        `userSecurityQuestionAnswer` = :userSecAnswer
    10. ");
    11.  
    I think this only works with MySQL, though.
    nico_swd, Nov 14, 2013 IP
  17. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #17
    @nico_swd, you don't need single quotes inside the query unless your values have spaces... which is why with PDO::prepare you can use single spaces instead of doubles more easily.

    hence:
    Code (Text):
    1. if ($numberOfErrors == 0) {
    2.     $db = new PDO($dsn, $dbUserName, $dbPassword);
    3.     $statement = $db->prepare('
    4.         INSERT INTO userinfo (
    5.             userName, userPassword, userEmail, userSecurityQuestion, userSecurityAnswer
    6.         ) values (
    7.             :name, :pass, :mail, :securityQuestion, :securityAnswer
    8.         )
    9.     ');
    10.     $statement->execute([
    11.         ':name' => $_POST['userName'],
    12.         ':pass' => $_POST['password'],
    13.         ':mail' => $_POST['email'],
    14.         ':securityQuestion' => $_POST['securityQuestion'],
    15.         ':securityAnswer' => $_POST['securityAnswer']
    16.     ]);
    17. }
    Being the longhand version... as opposed to the shorthand version:

    Code (Text):
    1. if ($numberOfErrors == 0) {
    2.     $db = new PDO($dsn, $dbUserName, $dbPassword);
    3.     $statement = $db->prepare('
    4.         INSERT INTO userinfo (
    5.             userName, userPassword, userEmail, userSecurityQuestion, userSecurityAnswer
    6.         ) values (
    7.             ?, ?, ?, ?, ?
    8.         )
    9.     ');
    10.     $statement->execute([
    11.         $_POST['userName'],
    12.         $_POST['password'],
    13.         $_POST['email'],
    14.         $_POST['securityQuestion'],
    15.         $_POST['securityAnswer']
    16.     ]);
    17. }
    ... personally I hate bindparam, it's just ugly/long/silly.
    deathshadow, Nov 15, 2013 IP
  18. nico_swd

    nico_swd Prominent Member

    Messages:
    4,119
    Likes Received:
    331
    Best Answers:
    17
    Trophy Points:
    325
    #18
    I know they're not required, but other than the spaces thing they're also a good way of making the whole thing more future-proof, as they tell the engine that this is definitely a field name (or table name), and not a possible reserved keyword. Not that I think any of the fields above will become a reserved keyword at some point, but the backticks were already in his code and there was no harm in leaving them in there.
    Last edited: Nov 16, 2013
    nico_swd, Nov 16, 2013 IP
  19. deathshadow

    deathshadow Prominent Member

    Messages:
    5,980
    Likes Received:
    827
    Best Answers:
    144
    Trophy Points:
    395
    #19
    Yeah, I'm from the old world of programming where the proper response to someone using a reserved word is akin to Joe six-pack's response to someone wearing a Red Sox jersey into Brooklyn... "Whaddaya, freekin retahded?!?" :D
    deathshadow, Nov 17, 2013 IP
    nico_swd likes this.
  20. nico_swd

    nico_swd Prominent Member

    Messages:
    4,119
    Likes Received:
    331
    Best Answers:
    17
    Trophy Points:
    325
    #20
    Hahaha, I see your point. But sometimes it occurs that they add new keywords. For instance, I remember (way back) I used to have a field called "range", and after an update it became a reserved keyword and it broke my code.

    So I see no harm in using back ticks. They may not be required, but I tend to use them anyway.
    nico_swd, Nov 17, 2013 IP