sql error while trying to store the image to database(UPDATED)

Question

UPDATED: As suggested in comment section I have updated my code and using PDO now. I still have the same error. I am trying to store multiple images to the database (phpmyadmin). When I try to upload it gives an error Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'file.png' in 'field list'

My database:

I have an database named image_upload and inside it has a table named car_detailss which has an id (int11), car_name(varchar 255) and imageOfcar(longblob).

That's my image details:

Array
(
    [0] => Array
        (
            [name] => file.png
            [type] => image/png
            [tmp_name] => /opt/lampp/temp/phpJYyrQn
            [error] => 0
            [size] => 77776
        )

    [1] => Array
        (
            [name] => files.png
            [type] => image/png
            [tmp_name] => /opt/lampp/temp/phpXOLvzL
            [error] => 0
            [size] => 84710
        )

)

As suggested in comment section I am using PDO now That's my code: (UPDATED)

    <!doctype html>
<html>
    <head>
        <title>Test</title>
    </head>
    <body>
    <form   method="post" enctype="multipart/form-data">
           <input type="file" name="userfile[]" multiple="" /> 
           <input type="submit" name="submit" value="upload" />
    </form>

    <?php
      $servername="localhost";
      $username="root";
      $password = "";
      $dbname="image_upload";
      try {
      $conn = new PDO("mysql:host=$servername;dbname=$dbname",$username, $password);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      // begin the transaction
      $conn->beginTransaction();


      
      

$phpFileUploadErrors = array(
    0 => "There is no error, the file uploaded with success",
    1 => "The uploaded file exceeds the upload_max_filesize directive in php.ini",

    2 => "The upload file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form",

    3 => "The upload file was only partially uploaded",


    4 => "No file was uploaded",

    6 => "Missing a temporary folder",
    7 => "Failed to write file to disk.",
    8 => "A php extension stopped the file upload"
);



    if(isset($_FILES['userfile'])) {
      
       $file_array = reArrayFiles($_FILES['userfile']);
       pre_r($file_array);
        for($i=0;$i<count($file_array);$i++) {

    $carname = $file_array[$i]["tmp_name"];
    $tablename = "car_detailss";

            $imagename = $file_array[$i]["name"];
            pre_r($carname);
            pre_r($imagename);
            $conn->exec("INSERT INTO `car_detailss`(`car_name`, `imageOfcar`) VALUES ( $carname,$imagename)");
          
          
          
            }
           
         
         

        }
        $conn->commit();
        echo "new records created succesfully";
    } catch(PDOException $e) {
        // roll back the transaction if something failed
        $conn->rollback();
        echo "Error: " . $e->getMessage();
      }
      
      $conn = null;
       
    

      
 
    

 function reArrayFiles($file_post) {
     $file_ary = array();
     $file_count = count($file_post['name']);
     $file_keys = array_keys($file_post);

     for ($i = 0; $i < $file_count; $i++){
     foreach($file_keys as $key) {
         $file_ary[$i][$key] = $file_post[$key][$i];
     
     }
 }
 return $file_ary;
}

    function pre_r($array) {
        echo '<pre>';
        print_r($array);
        echo '</pre>';
    }

    
    ?>
    
    </body>
</html>

How can I fix the error it displays (at the top) I mentioned?

You didn't post the corresponding query `INSERT INTO car_detailss....` . However I hope you don't use roor access (without a password) in your production system — B001ᛦ, Jul 20 '20 at 00:53
@B001ᛦ sorry, I edited something in code and forget to change it back before posting it here. I have edited the code again. Right now I am using without a password (as its just for practice) When I actual use the code somewhere in real life I will add a password. — , Jul 20 '20 at 00:58
`multi_query` with your SQL injection problem is asking for a BIG problem. Also fixing SQL injection will fix current problem. — user3783243, Jul 20 '20 at 01:02
Does this answer your question? [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — user3783243, Jul 20 '20 at 01:02
... or a dup of https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql but that just pushes the problem further down the road. — user3783243, Jul 20 '20 at 01:03
@user3783243 I have updated my code and used PDO. I still have the same error — , Jul 20 '20 at 01:21
Your code is still insecure and has the same issue. Look at either dup. — user3783243, Jul 20 '20 at 01:29
@user3783243 can you please check my answer? It works now but is it secure? — , Jul 20 '20 at 01:45
It is not secure please see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php, prepared statements with parameterized queries must be used. Just changing to PDO offers no additional security. — user3783243, Jul 20 '20 at 02:10

score 1 · Accepted Answer · answered Jul 20 '20 at 01:43

As suggested in the comment section. I first changed my code to PDO and instead of inserting into database without any backticks or "", I followed the suggestion in comment section and added backticks and "" while inserting the images into database.

$conn->exec( "INSERT INTO car_detailss (car_name, imageOfcar) VALUES ('$carname', '$imagename')");

Now I am able to insert the images to my database. I have updated my code here as well.

My code:

<!doctype html>
<html>
    <head>
        <title>Test</title>
    </head>
    <body>
    <form   method="post" enctype="multipart/form-data">
           <input type="file" name="userfile[]" multiple="" /> 
           <input type="submit" name="submit" value="upload" />
    </form>

    <?php
      $servername="localhost";
      $username="root";
      $password = "";
      $dbname="image_upload";
      try {
      $conn = new PDO("mysql:host=$servername;dbname=$dbname",$username, $password);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      // begin the transaction
      $conn->beginTransaction();


      
      

$phpFileUploadErrors = array(
    0 => "There is no error, the file uploaded with success",
    1 => "The uploaded file exceeds the upload_max_filesize directive in php.ini",

    2 => "The upload file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form",

    3 => "The upload file was only partially uploaded",


    4 => "No file was uploaded",

    6 => "Missing a temporary folder",
    7 => "Failed to write file to disk.",
    8 => "A php extension stopped the file upload"
);



    if(isset($_FILES['userfile'])) {
      
       $file_array = reArrayFiles($_FILES['userfile']);
       pre_r($file_array);
        for($i=0;$i<count($file_array);$i++) {

    $carname = $file_array[$i]["size"];
    $tablename = "car_detailss";

            $imagename = $file_array[$i]["name"];
           
            $conn->exec( "INSERT INTO `car_detailss` (`car_name`, `imageOfcar`) VALUES ('$carname', '$imagename')");

          
          
          
            }
           
         
         

        }
        $conn->commit();
        echo "new records created succesfully";
    } catch(PDOException $e) {
        // roll back the transaction if something failed
        $conn->rollback();
        echo "Error: " . $e->getMessage();
      }
      
      $conn = null;
       
    

      
 
    

 function reArrayFiles($file_post) {
     $file_ary = array();
     $file_count = count($file_post['name']);
     $file_keys = array_keys($file_post);

     for ($i = 0; $i < $file_count; $i++){
     foreach($file_keys as $key) {
         $file_ary[$i][$key] = $file_post[$key][$i];
     
     }
 }
 return $file_ary;
}

    function pre_r($array) {
        echo '<pre>';
        print_r($array);
        echo '</pre>';
    }

    
    ?>
    
    </body>
</html>

sql error while trying to store the image to database(UPDATED)

1 Answers1