Insert and store in database file in table with a column of type [image]

Question

Problem: I'm trying to upload a image directly to a database table in this column logo [image] NULL

This file is the register.php

require __DIR__ . '/lib/library.php';
$app = new Users();

$login_error_message = '';
$register_error_message = '';

// check Register request
if (!empty($_POST['btnRegister'])) {
    if ($_POST['name'] == "") {
        $register_error_message = 'Name field is required!';
    } else if ($_POST['email'] == "") {
        $register_error_message = 'Email field is required!';
    } else if ($_POST['tel_number'] == "") {
        $register_error_message = 'Nº Telemóvel field is required!';
    } else if ($_POST['login'] == "") {
        $register_error_message = 'Username field is required!';
    } else if ($_POST['nif'] == "") {
        $register_error_message = 'NIF field is required!';
    } else if ($_POST['role_id'] == "") {
        $register_error_message = 'Perfil field is required!';
    }else if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
        $register_error_message = 'Invalid email address!';
    } else if ($app->isEmail($_POST['email'])) {
        $register_error_message = 'Email is already in use!';
    } else if ($app->isUsername($_POST['login'])) {
        $register_error_message = 'Username is already in use!';
    } else {
        }
            if(getimagesize($_FILES['logo']['tmp_name'])==FALSE){
                $image = NULL;
             }else{
                $image = $_FILES['logo']['tmp_name'];
                $image = addslashes(file_get_contents($image));

        $user_id = $app->Register($_POST['name'], $_POST['email'], $_POST['tel_number'], $_POST['nif'], $_POST['password'], $_POST['role_id'], $_POST['login'], $image);
        // set session and redirect user to the profile page
        $_SESSION['user_id'] = $user_id;
        //header("Location: profile.php");
    }
}

This function is responsible for doing the insert into the database, as you can see I'm trying to insert the image as a Blob, but whenever I try to do an insert in the table and the logo files existe it returns the following error: Array ( [0] => IMSSP [1] => -7 [2] => An error occurred translating string for input param 8 to UCS-2: Error code 0x0 )

If I dont submit with a logo file it returns a different error: getimagesize(): Filename cannot be empty

public function Register($name, $email, $tel_number, $NIF, $password, $role_id, $login, $logo)
    {

        try {
            $db = DB();
            $query = $db->prepare("INSERT INTO start_users(name, email, tel_number, NIF, psw, role_id, login, logo) VALUES (:name,:email,:tel_number,:NIF,:psw,:role_id,:login,:logo)");
            $query->bindParam("name", $name, PDO::PARAM_STR);
            $query->bindParam("email", $email, PDO::PARAM_STR);
            $query->bindParam("tel_number", $tel_number, PDO::PARAM_INT);
            $query->bindParam("NIF", $NIF, PDO::PARAM_INT);
            $enc_password = password_hash ($password, PASSWORD_DEFAULT);
            $query->bindParam("psw", $enc_password, PDO::PARAM_STR);
            $query->bindParam("role_id", $role_id, PDO::PARAM_INT);
            $query->bindParam("login", $login, PDO::PARAM_INT);
            $query->bindParam("logo", $logo, PDO::PARAM_LOB);
            $query->execute();
            print_r($query->errorInfo());
            return $db->lastInsertId();
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }

Part of the form that throws the error

 <form action="" method="post" enctype="multipart/form-data">

 <div class="form-group">
                    <label for="">Logo</label>
                    <input type="file" name="logo" accept="image/x-png,image/gif,image/jpeg" class="form-control"/>
                </div>

                <div class="form-group">
                    <input type="submit" name="btnRegister" class="btn btn-primary" value="Register"/>
                </div>

</form>

Answer 1

Explanations:

If your question is tagged correctly and you use PHP Driver for SQL Server (sqlsrv tag), then you need to specify binary encoding for the :logo parameter:

$query->bindParam(":logo", $logo, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY); 

You may also consider the following:

• The image data type will be removed in a future version of SQL Server and should be replaced with varbinary(max) data type.
• Use a parameter name in the following format :name if you use named placeholders.

PHP (based on your code):

<?php
public function Register($name, $email, $tel_number, $NIF, $password, $role_id, $login, $logo)
    {
        try {
            $db = DB();
            $query = $db->prepare("INSERT INTO start_users(name, email, tel_number, NIF, psw, role_id, login, logo) VALUES (:name,:email,:tel_number,:NIF,:psw,:role_id,:login,:logo)");
            $query->bindParam(":name", $name, PDO::PARAM_STR);
            $query->bindParam(":email", $email, PDO::PARAM_STR);
            $query->bindParam(":tel_number", $tel_number, PDO::PARAM_INT);
            $query->bindParam(":NIF", $NIF, PDO::PARAM_INT);
            $enc_password = password_hash ($password, PASSWORD_DEFAULT);
            $query->bindParam(":psw", $enc_password, PDO::PARAM_STR);
            $query->bindParam(":role_id", $role_id, PDO::PARAM_INT);
            $query->bindParam(":login", $login, PDO::PARAM_INT);
            $query->bindParam(":logo", $logo, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY);
            $query->execute();
            print_r($query->errorInfo());
            return $db->lastInsertId();
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }
?>

Working example:

<?php
# Connection info
$server   = 'server\instance';
$database = 'database';
$uid      = 'username';
$pwd      = 'password';

# Connection
try {
    $dbh = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die("Error connecting to SQL Server. ".$e->getMessage());
}

# Insert data
try {
    $logo = file_get_contents('image.jpg');
    $stmt = $dbh->prepare("INSERT INTO start_users(logo) VALUES (:logo)");
    $stmt->bindParam(":logo", $logo, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY);
    $result = $stmt->execute();
    if ($result === false) {
        die( "Error executing stored procedure.");
    }   
} catch( PDOException $e ) {
    die( "Error executing stored procedure: ".$e->getMessage());
}

# End
$stmt = null;
?>