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.

Quick PDO Question

Discussion in 'PHP' started by scottlpool2003, Mar 8, 2013.

  1. #1
    I need to retrieve the last insert ID, but can't use lastInsertID() as this version of PDO doesn't have the correct drivers.

    I'd prefer not to use a separate query if possible but found that using the following, I can return the previous row;

    $sth->fetchColumn();
    PHP:
    If I amend it to;

    $sth->fetchColumn()+1;
    PHP:
    Will I get the same result of lastInsertID or will I just get the next row?

    I need the actual row ID of the inserted row and can't run the risk of 2 users inserting at the same time and there being a conflicted ID as the next procedure is to create a folder using that ID.
     
    scottlpool2003, Mar 8, 2013 IP
    procrusher likes this.
  2. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #2
    I'd really advise against that, it's not connection specific so it could indeed cause a conflict.

    I have to ask though -- Is it the version of PDO, or the SQL engine? Which SQL engine? Can you upgrade to something not made in the dark ages?

    Does $db->query('SELECT LAST_INSERT_ID()'); return the last inserted ID properly? (that's mySQL specific)

    if it's MS SQL you could try $db->query('SELECT SCOPE_IDENTITY()');

    In PostGre you can use 'RETURNING', but I've really not studied that one enough to advise the best approach.

    -- edit -- I just studied it. In PostGre or Oracle, you'd do:

    
    INSERT INTO tableName (
      name,dummy,whatever
    ) VALUES (
      :name, :dummy, :whatever
    )
    RETURNING id
    Code (markup):
    a $statement->fetchColumn() would then retrieve the ID -- again, that's PostGre and/or Oracle specific.
     
    deathshadow, Mar 8, 2013 IP
  3. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #3
    I think it could possibly be the version of PDO


    I am MySQL compatible, but I'm running this particular database on SQL although on other domains I use MySQL.

    SQL Engine:

     
    scottlpool2003, Mar 11, 2013 IP
  4. D3Tek

    D3Tek Active Member

    Messages:
    164
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    50
    #4
    Wait, are you saying you can't use:

    $sth->lastInsertId();
    PHP:
    ?
     
    D3Tek, Mar 11, 2013 IP
  5. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #5
    Yes:



     
    scottlpool2003, Mar 11, 2013 IP
  6. D3Tek

    D3Tek Active Member

    Messages:
    164
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    50
    #6
    As deathshadow suggested, I'd use SCOPE_IDENTITY.

    
     
    $sth->query("SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)"); 
    $sth->execute(); 
    $result = $sth->fetch(); 
    echo $result[0]; 
    
    PHP:
    This is a fix floating around the net.
     
    D3Tek, Mar 11, 2013 IP
    procrusher likes this.
  7. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #7
    When I use that, I then get;
    //Insert data into the db
      $sth = $dbconn->prepare("
      INSERT INTO publication
        (title, text, cat, user_id, use_ad, viewcount, tags)
      VALUES (:title, :text, :cat, :user_id, :use_ad, :viewcount, :tags)
      ");
     
    //Run the insert
      if ($sth->execute(array(
      ":title" => $_POST['title'],
      ":text" => $_POST['description'],
      ":cat" => $_POST['category'],
      ":user_id" => $userid,
      ":use_ad" => 0,
      ":viewcount" => 0,
      ":tags" => $_POST['keywords']
    ))) {
    //Success
       echo "Inserted correctly";
    //Return last ID
        $sth->query("SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)");
        $sth->execute();
        $result = $sth->fetch();
        print $result[0]; 
    }
    else {
            //There was a problem
            echo "There was a problem";
    }
    PHP:
     
    scottlpool2003, Mar 11, 2013 IP
  8. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #8
    Never mind I needed to change:
    $sth->query("SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)");
    PHP:
    To:
    $sth = $dbconn->query("SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)"); 
    PHP:
     
    scottlpool2003, Mar 11, 2013 IP
  9. D3Tek

    D3Tek Active Member

    Messages:
    164
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    50
    #9
    Good, glad it worked!
     
    D3Tek, Mar 11, 2013 IP
  10. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #10
    another minor flub -- you're calling query, then doing a execute -- you only do execute after a prepare -- both query and 'exec' don't need execute, they run immediately.
     
    deathshadow, Mar 11, 2013 IP
  11. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #11
    Thanks, so completed code should look something like:

    <?php
    if(isset($_POST['createpub']))  {
     
    //Insert data into the db
      $sth = $dbconn->prepare("
      INSERT INTO publication
        (title, text, cat, user_id, use_ad, viewcount, tags)
      VALUES (:title, :text, :cat, :user_id, :use_ad, :viewcount, :tags)
      ");
     
     
    //Run the insert
      if ($sth->execute(array(
      ":title" => $_POST['title'],
      ":text" => $_POST['description'],
      ":cat" => $_POST['category'],
      ":user_id" => $userid,
      ":use_ad" => 0,
      ":viewcount" => 0,
      ":tags" => $_POST['keywords']
     
    ))) {
     
    //Get last ID
        $sth = $dbconn->query("SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)");
        $result = $sth->fetch();
        //Create folder for this publication
        mkdir("../publication/$userid/$result[0]");
      }
    }
     
    //Close connection
    $dbconn = null;
    ?>
    PHP:
     
    scottlpool2003, Mar 11, 2013 IP
  12. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #12
    Since you're only fetching a number result, you can use fetchColumn instead of pulling the full array.

    $lastID = $sth->fetchColumn();

    fetchColumn is very handy when you only have one result... like in this case, or when using operations like count(*)

    Though your code wouldn't run on my server, but that's because I disable mkdir in my php.ini for security reasons.
     
    deathshadow, Mar 11, 2013 IP
  13. D3Tek

    D3Tek Active Member

    Messages:
    164
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    50
    #13

    I don't see why you'd disable mkdir() for security reasons.
    The only situation I can see mkdir() being bad is by setting wrong permissions or using unsafe paths to create a directory. But that's all server side and the person who is using that code's problem.
     
    D3Tek, Mar 11, 2013 IP
  14. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #14
    What are the risks with mkdir()? I have an individual user setup specifically for 1 folder with write permissions to create folders within that folder. I thought doing it that was was safe along with restricting the file types of that folder.

    Would there be another safer way to do it? Also the user has no input as to the naming structure, that's why I've been trying to get the last ID so the user has no controll.
     
    scottlpool2003, Mar 11, 2013 IP
  15. D3Tek

    D3Tek Active Member

    Messages:
    164
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    50
    #15

    There are no security risks of mkdir() as I said. I think deathshadow may just be being ultra safe, removing anything that isn't fundamental. In all my years in security, I've never known anyone have an issue with mkdir().

    All you can do is make sure permissions are correct, and the numbers like $userid, and $result[0] are clean. And obviously $result[0] will be.

    I don't know how you're setting $userid, but you can check it is actually a number using ctype_digit()

    Hope this helps.
     
    D3Tek, Mar 11, 2013 IP
  16. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #16
    Any directory created by PHP would have PHP as it's owner - unless you're using suPHP in which case it would be owned by the account - and as such have full write permissions... defeating the point of *nix filesystem permissions - just like suPHP does -- since suPHP is the security equivalent of shooting your dog because the neighbor's cat has fleas.

    GAH! suPHP - Lock out other directories that should already be locked out via the permission system, by logging in as the account user... meaning the ENTIRE user account might as well be read/write/exec (7) to PHP? ... and this is supposed to make things more secure HOW exactly?!? Make it impossible for a site to hack another site on the server, by effectively destroying each site's internal security... RIGHT.

    I block a LOT of things that to be frank, I don't think PHP really has any business doing.

    disable_functions = 'ini_alter,ini_restore,ini_set,phpinfo,chdir,chgrp,chmod,chown,fputs,fwrite,lchgrp,mkdir,tmpfile,touch,link,symlink,exec,passthru,proc_open,shell_exec,system'

    ... and a LOT of that is just making sure that the SQL un/pw/host info cannot be accessed except by my 'one index to rule them all' -- I used to block readfile and file_get_contents, but now I just set a whitelist of where PHP is allowed to access anything:

    open_basedir = './libraries/:./modules/:./resources/'

    ... instead of 'wildly redirecting' to my one index I whitelist certain file types and redirect EVERY other request to index.php:

    # redirect all 'unknown' requests to index.php
    RewriteEngine On
    RewriteRule !\.(gif|jpg|png|css|js|html|htm|txt|ico|zip|gz|rar|pdf|xml|wav|mp3|mp4|mpg|flv|swf|mkv|ogg|avi|webm|woff|svg|eot|ttf)$ index.php

    Going hand in hand with my dbsettings.php file only allowing itself to be called by the main() function in my index.php via debug_backtrace

    <?php
    
     function dbSettings() {
         dbSecurityCheck();
         return array(
         
             /* begin user edits */
             
             'dsn' = 'mysql:host=localhost;dbname=paladin',
             'dbUser' = 'paladin_user',
             'dbPass' = 'haptichopper'
             
             /* do not edit past this point! */
             
         );
     }
    
     function dbSecurityCheck() {
         if (!defined('passwordSent')) {
             define('passwordSent',1);
             $d = debug_backtrace();
             if (
                 isset($d[1]) &&
                 ($d[1]['function'] == 'main') && 
                 (str_replace(
                         '/dbSettings.php',
                         '/index.php',
                         str_replace('/','\\',$d[0]['file'])
                     ) == str_replace('/','\\',$d[1]['file'])
                 )
             ) return true;
         }
         die ('Hacking attempt detected for dbSettings.php!');
     }
    
     ?>
    Code (markup):
    Admittedly, this is a bit 'over the top' but 99% of the hacked sites I've cleaned up for people the past decade where the issue was a code elevation, the hacks would have fallen flat on their face with these in place.
     
    deathshadow, Mar 11, 2013 IP
  17. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #17
    With me being on a Windows 2008 server, I don't need to worry about Mod Rewrite as .htaccess isn't valid if I'm correct? And as with open_basedir, the only folder permitted I have set is on a d: partition rather than the c: partition and in a tmp directory.

    Would this be considered safe?

    Also, I'm not entirely sure with Windows server but if my memory is correct, the alternative for the .htaccess file is the .conf file which is only accessible directly on the server.

    I may be totally wrong here though.

    It is quite confusing, there's not a whole lot of difference between things on a Linux -> Windows server but every small thing, I tend to run into.
     
    scottlpool2003, Mar 12, 2013 IP
  18. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #18

    The above which I've set in italic...

    $userid is generated from a PDO query. I use $userid to get the users ID from a loggedin session and extract the users data from the SQL database.
     
    scottlpool2003, Mar 12, 2013 IP
  19. D3Tek

    D3Tek Active Member

    Messages:
    164
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    50
    #19

    Okay. So you're fine. Just make sure that $userid is safe. That's all.

    And like I said earlier, deathshadow has disabled things that are not needed in his eyes or could have a potential security risk. I see where he's coming from about the mkdir() situation, and I knew from the start that would roughly be the argument, but as long as you make sure permissions are correct, you will be fine using mkdir(). There is not a better alternative for mkdir() either.
     
    D3Tek, Mar 13, 2013 IP