Loading PDF files into a sql database

[tfenge]

Hello ,  I am quite new to PHP and this forum, please can I have some guidance on the below. 

I am building a web app that has a form to select a file, and a button to upload. On click the upload the button calls this PHP file "push_ExistingInjuryFile.php". (See below)

   <form action="push_ExistingInjuryFile.php" method="POST" enctype="multipart/form-data" style="position: absolute; top: 1190px; left: 125px;"">
        <input type="file" name="pdfFile">
        <input type="submit" name="uploadBtn" value="Upload">
    </form>

This calls the below file 

<?php

session_start(); // Start the session

// Database connection parameters
$serverName = "a";
$dbName = "a";
$username = "a";
$password = "a";
$tableName = "Existing_Injury_Files";

// Establish the database connection
$conn = new PDO("sqlsrv:Server=$serverName;Database=$dbName", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_FILES["pdfFile"])) {
    echo "Step 1: Form submitted and file uploaded successfully.<br>";

    $allowedFormats = ["pdf"];
    $fileName = $_FILES["pdfFile"]["name"];
    $fileContent = file_get_contents($_FILES["pdfFile"]["tmp_name"]);
    $fileExtension = pathinfo($fileName, PATHINFO_EXTENSION);
    $fileBaseName = pathinfo($fileName, PATHINFO_FILENAME);

    // Fetch the session ID from the session data
    if (!isset($_SESSION['username'])) {
        die("Error: User not authenticated."); // Or redirect to the login page
    }

    echo "Step 2: Session ID fetched from session data successfully.<br>";

    // Assuming your session stores the user's session ID in $_SESSION['username']
    // Replace 'username' with the appropriate key where you store the session ID.
    $sessionId = $_SESSION['username'];

    if (!in_array($fileExtension, $allowedFormats)) {
        die("Error: Only PDF files are allowed.");
    }

    echo "Step 3: File extension validation successful.<br>";

    // Rest of your code for validating file name and database insertion
    // ...

    // Replace these variables with your actual database connection details
    $serverName = "a";
    $database = "a";
    $username = "a";
    $password = "a";

    try {
        $conn = new PDO("sqlsrv:Server=$serverName;Database=$database", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Prepare and execute the stored procedure to check if the file name exists
        $sql = "EXEC dbo.ValidFileName @Name = ?, @SessionId = ?";
        $stmt = $conn->prepare($sql);

        $stmt->bindParam(1, $fileBaseName, PDO::PARAM_STR);
        $stmt->bindParam(2, $sessionId, PDO::PARAM_STR, null, PDO::SQLSRV_ENCODING_BINARY);
        $stmt->execute();

        echo "Step 4: Stored procedure execution successful.<br>";

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        // Check the result returned by the stored procedure
        if ($result && $result['Result'] === 0) {
            die("Error: The file name '$fileBaseName' does not exist in the database for the current session.");
        }

        echo "Step 5: File name validation against database successful.<br>";

        // If the file name exists, proceed with the database insertion
        $sql = "INSERT INTO Existing_Injury_Files (FileName, FileContent, DateAdded,Child_LastName) VALUES (:fileName, :fileContent, :dateAdded, :childLastName)";

        $stmt = $conn->prepare($sql);

        $stmt->bindParam(':fileName', $file['name']);
        $stmt->bindParam(':fileContent', $fileContent, PDO::PARAM_LOB);
        $dateAdded = date('Y-m-d H:i:s');
        $stmt->bindParam(':dateAdded', $dateAdded);
		$stmt->bindParam(':childLastName', $file['name']);

        $stmt->execute();

        echo "Step 6: Database insertion successful.<br>";

        // Redirect to a page or display a success message
        header("Location: index.php");
        exit();
    } catch (PDOException $e) {
        die("Database Error: " . $e->getMessage());
    }
}
?>

 

The above uses a SQL SP ValidFilName which will return either a 1 or 0 if the file is valid or not. Basically the File name should be the surname of that stored in a list, and it uses the username from the SESSION to find out what business unit we are working on. 

The SQL table I want to insert into is below. 
 

CREATE TABLE Existing_Injury_Files (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  FileName VARBINARY(255),
  FileContent VARBINARY(MAX),
  DateAdded DATETIME DEFAULT GETDATE(),
  Child_LastName nvarchar(25)
);

 

Now that all this information has been provided , the error I get is as follows 

Step 1: Form submitted and file uploaded successfully.
Step 2: Session ID fetched from session data successfully.
Step 3: File extension validation successful.
Step 4: Stored procedure execution successful.
Step 5: File name validation against database successful.
Database Error: SQLSTATE[IMSSP]: An error occurred translating string for input param 2 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page.

 

I hope this is a simple fix, many thanks to anyone wishing to take a look.

 

 

 

 

 

[ginerjm]

Without reading thru your text and only looking at the title, you should not be storing your file in a database.  Simply store them in a folder (or folders) and have a table that contains the identifying info that you need.    Re-think your plan.

[kicken]

2 hours ago, tfenge said:

$stmt->bindParam(':fileContent', $fileContent, PDO::PARAM_LOB);

Try

$stmt->bindParam(':fileContent', $fileContent, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);

 

[I-AM-OBODO]

It's always best if you create a folder to store the files and then you save the path in the database.

[ginerjm]

Already said.