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