[SOLVED] mysql_insert_id() woes

[larsbrimmer]

Hi,

 

I am working on this upload script. I want to rename the file to the AUTO_INCREMENT ID. Although the file name is inserted to the table I am only concerned about the actual file.

 

I tried replacing

 

md5(uniqid(rand(), true))

with

mysql_insert_id()

 

but it didn't work because I think it is before the quarry.

 

Full code

 

// Allow script to work long enough to upload big files (in seconds, 2 days by default)
@set_time_limit(172800);

// following may need to be uncommented in case of problems
// ini_set("session.gc_maxlifetime","10800");

function showUploadForm($message='') {
  $max_file_size_tag = '';
  if (MAX_FILE_SIZE > 0) {
    // convert to bytes
    $max_file_size_tag = "<input name='MAX_FILE_SIZE' value='".(MAX_FILE_SIZE*1024)."' type='hidden' >\n";
  }

  // Load form template
  include ('file-upload.html');
}

// errors list
$errors = array();

$message = '';

// we should not exceed php.ini max file size
$ini_maxsize = ini_get('upload_max_filesize');
if (!is_numeric($ini_maxsize)) {
  if (strpos($ini_maxsize, 'M') !== false)
    $ini_maxsize = intval($ini_maxsize)*1024*1024;
  elseif (strpos($ini_maxsize, 'K') !== false)
    $ini_maxsize = intval($ini_maxsize)*1024;
  elseif (strpos($ini_maxsize, 'G') !== false)
    $ini_maxsize = intval($ini_maxsize)*1024*1024*1024;
}
if ($ini_maxsize < MAX_FILE_SIZE*1024) {
  $errors[] = "Alert! Maximum upload file size in php.ini (upload_max_filesize) is less than script's MAX_FILE_SIZE";
}

// show upload form
if (!isset($_POST['submit'])) {
  showUploadForm(join('',$errors));
}

// process file upload
else {
  
  while(true) {

    // make sure destination folder exists
    if (!@file_exists(DESTINATION_FOLDER)) {
      $errors[] = "Destination folder does not exist or no permissions to see it.";
      break;
    }

    // check for upload errors
    $error_code = $_FILES['filename']['error'];
    if ($error_code != UPLOAD_ERR_OK) {
      switch($error_code) {
        case UPLOAD_ERR_INI_SIZE: 
          // uploaded file exceeds the upload_max_filesize directive in php.ini
          $errors[] = "File is too big (1).";
          break;
        case UPLOAD_ERR_FORM_SIZE: 
          // uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form
          $errors[] = "File is too big (2).";
          break;
        case UPLOAD_ERR_PARTIAL:
          // uploaded file was only partially uploaded.
          $errors[] = "Could not upload file (1).";
          break;
        case UPLOAD_ERR_NO_FILE:
          // No file was uploaded
          $errors[] = "Could not upload file (2).";
          break;
        case UPLOAD_ERR_NO_TMP_DIR:
          // Missing a temporary folder
          $errors[] = "Could not upload file (3).";
          break;
        case UPLOAD_ERR_CANT_WRITE:
          // Failed to write file to disk
          $errors[] = "Could not upload file (4).";
          break;
        case 8:
          // File upload stopped by extension
          $errors[] = "Could not upload file (5).";
          break;
      } // switch

      // leave the while loop
      break;
    }

    // get file name (not including path)
    $filename = @basename($_FILES['filename']['name']);

    // filename of temp uploaded file
    $tmp_filename = $_FILES['filename']['tmp_name'];

    $file_ext = @strtolower(@strrchr($filename,"."));
    if (@strpos($file_ext,'.') === false) { // no dot? strange
      $errors[] = "Suspicious file name or could not determine file extension.";
      break;
    }
    $file_ext = @substr($file_ext, 1); // remove dot

    // check file type if needed
    if (count($exts)) {   /// some day maybe check also $_FILES['user_file']['type']
      if (!@in_array($file_ext, $exts)) {
        $errors[] = "Files of this type are not allowed for upload.";
        break;
      }
    }

    // destination filename, rename if set to
    $dest_filename = $filename;
    if (RENAME_FILE) {
      $dest_filename = md5(uniqid(rand(), true)) . '.' . $file_ext;
    }
    // append predefined string for safety
    $dest_filename = $dest_filename . APPEND_STRING;

    // get size
    $filesize = intval($_FILES["filename"]["size"]); // filesize($tmp_filename);

    // make sure file size is ok
    if (MAX_FILE_SIZE > 0 && MAX_FILE_SIZE*1024 < $filesize) {
      $errors[] = "File is too big (3).";
      break;
    }

    if (!@move_uploaded_file($tmp_filename , DESTINATION_FOLDER . $dest_filename)) {
      $errors[] = "Could not upload file (6).";
      break;
    }

    if (DO_LOG) {
      // Establish DB connection
      $link = @mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
      if (!$link) {
        $errors[] = "Could not connect to mysql.";
        break;
      }
      $res = @mysql_select_db(DB_DATABASE, $link);
      if (!$res) {
        $errors[] = "Could not select database.";
        break;
      }
      $m_ip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
      $m_size = $filesize;
      $m_fname = mysql_real_escape_string($dest_filename);
      $sql = "insert into _uploads_log (log_filename,log_size,log_ip) values ('$m_fname','$m_size','$m_ip')";
      $res = @mysql_query($sql);
      if (!$res) {
        $errors[] = "Could not run query.";
        break;
      }
      @mysql_free_result($res);
      @mysql_close($link);
    } // if (DO_LOG)


    // redirect to upload success url
    header('Location: ' . SUCCESS_URL);
    die();

    break;

  } // while(true)

  // Errors. Show upload form.
  $message = join('',$errors);
  showUploadForm($message);

}

 

Thanks,

 

Lars

[Mchl]

mysql_inser_id() returns ID of _last_ inserted record, so you can only use it _after_ executing query.

 

You should:

1. Upload a file to a temporary location

2. Execute query

3. Get last id

4. Rename file to a new name using this id

[BioBob]

Also, dont use MD5 anymore.  Its been cracked all to hell and back.  Use SHA1 and salt it.

 

$salt = "some string common to everything to be encrypted";

sha1($string_to_hide . $salt);

 

for what Mchl said, it goes like this:

 

$query = "SELECT * FROM table";

$sql = mysql_query($query);

 

$last_id = mysql_insert_id();

 

And thats it in a nutshell...

[larsbrimmer]

So I added this:

 

$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$query = "SELECT * FROM _uploads_log";
$sql = mysql_query($query);

$last_id = mysql_insert_id();

    $dest_filename = $filename;
    $dest_filename = $last_id . '.' . $file_ext;

 

Now it will upload the file but the file name is always zero and now no new records in the database.

 

Where did I go wrong?

 

Thanks

[gevans]

You need to select a database, look at mysql_select_db()

 

It saves as zero because there is no id yet

[Mchl]

BioBob: mysql_insert_id() returns last id INSERTED during current connection. Calling it after SELECT makes no sense.

 

And MD5 is still good for generating nonsecure hashes, that just need to act as (quasi)unique identifiers.

[larsbrimmer]

Thanks for the help. I think I am getting there but still have issues.

 

I change the order of the code and now I get the file uploaded and named to the ID.

A record is created in the DB but the log_filename is empty. Is it still possible grab the ID and use it to add the file name to the record at the same time.

 

new code

      // Establish DB connection
      $link = @mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
      if (!$link) {
        $errors[] = "Could not connect to mysql.";
        break;
      }
      $res = @mysql_select_db(DB_DATABASE, $link);
      if (!$res) {
        $errors[] = "Could not select database.";
        break;
      }
      $m_ip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
      $m_size = $filesize;
      $m_fname = mysql_real_escape_string($dest_filename);
      $sql = "insert into _uploads_log (log_filename,log_size,log_ip) values ('$m_fname','$m_size','$m_ip')";
      $res = @mysql_query($sql);
      if (!$res) {
        $errors[] = "Could not run query.";
        break;
      }

$lastItemID = mysql_insert_id();
    // destination filename, rename if set to
    $dest_filename = $filename;
    //if (RENAME_FILE) {
      $dest_filename = $lastItemID . '.' . $file_ext;
   // }
    // append predefined string for safety
    $dest_filename = $dest_filename . APPEND_STRING;

    // get size
    $filesize = intval($_FILES["filename"]["size"]); // filesize($tmp_filename);

    // make sure file size is ok
    if (MAX_FILE_SIZE > 0 && MAX_FILE_SIZE*1024 < $filesize) {
      $errors[] = "File is too big (3).";
      break;
    }

    if (!@move_uploaded_file($tmp_filename , DESTINATION_FOLDER . $dest_filename)) {
      $errors[] = "Could not upload file (6).";
      break;
    }

Thanks for your help.

[Mchl]

Not really. You have to run UPDATE query later.

[larsbrimmer]

Where do I put the UPDATE statement? In the same file?

 

How do I write the UPDATE statement?

 

Will this work

 

UPDATE `_uploads_log` SET log_filename WHERE log_id=$lastItemID"

 

Thanks

 

 

[uniflare]

You almost got it, you forgot to set the value to the filename on that query, but basically;

 

    [*]Create the Query String:

       

• 
         
• "UPDATE `_uploads_log` SET `log_filename`='$newfilename' WHERE `log_id`='$lastItemID'"
         

   

    [*]Execute the query with mysql_query();

       

• 
         
• mysql_query($query);
         

   

    [*]Place the update code after the insert query.

<?php

    // get size
    $filesize = intval($_FILES["filename"]["size"]); // filesize($tmp_filename);
    
    // Here maybe, then you can even add the filesize to the table, though i don't see any reason why you'd need to.
    
    // make sure file size is ok
    if (MAX_FILE_SIZE > 0 && MAX_FILE_SIZE*1024 < $filesize) {
      $errors[] = "File is too big (3).";
      break;
    }

?>

[larsbrimmer]

I tried

 

$query = "UPDATE `_uploads_log` SET `log_filename`='$newfilename' WHERE `log_id`='$lastItemID'";
mysql_query($query);

 

and

 

"UPDATE `_uploads_log` SET `log_filename`='$newfilename' WHERE `log_id`='$lastItemID'"
mysql_query($query);

 

and get this for both.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Thanks

 

 

[redarrow]

try

<?php
$query = "UPDATE _uploads_log SET log_filename='".mysql_real_escape_string($_POST['newfilename'])."' WHERE log_id='".mysql_real_escape_string($_POST['lastItemID'])."'";
$res=mysql_query($query)or die(mysql_error);
?>

[larsbrimmer]

I got it to work with this,

 

$query = "UPDATE `_uploads_log` SET `log_filename`='$dest_filename' WHERE `log_id`='$lastItemID'";
mysql_query($query);

 

 

Thanks everyone. Now I just need to add some other snippets of code that don't work yet.

-Lars