Laravel：Base64图像无法上传

Question

Using Laravel and PHP, I am trying to add a photo to a database. This database only takes base64 images.

I take a given file, verify that it's an image type (png, jpg, etc.) and try to encode it as a base64 image. Then I try to upload it to the database with other details about the object.

However, this isn't working. Instead I am told of a data type mismatch:

SQLSTATE[HY000]: General error: 20018 Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query. [20018] (severity 16)

My code absolutely works when I remove the 'photo' value from the insertion. So I'm wondering what it is that could be the problem with what I'm doing.

See below my laravel controller function.

public function addStudent(Request $request) {
    $courses = [1, 2, 3, 4, 5];
    $statuses = [1,2,3,4];

    $validated = Validator::make($request->all(), [
      "submit"      => "required",
      "student_id"  => ["required", "integer", "regex:/^[0-9]+$/"],
      "forename"    => ["required", "regex: /^[a-zA-Z’'. -]+$/"],
      "surname"     => ["required", "regex: /^[a-zA-Z’'. -]+$/"],
      "course_id"   => ["required", Rule::in([1, 2, 3, 4, 5]) ],
      "status_id"   => ["required", Rule::in([1, 2, 3, 4]) ],
      /*Image validation done here: must be of the types below
      Since this part passes, I know I am working with an image */
      "photo"       => "required|image|file:jpeg,png,jpg,gif,svg|max:2048"
]);
$errors = $validated->errors();
if($validated->fails()) {
  return redirect()->back()->withInput($request->all())->withErrors($errors);
}
$data = $request->all();

/* Here I try to encode the image as base64 */
if ($request->hasFile("photo")) {
  if($request->file("photo")->isValid()) {
    $file = $request->file('photo');
    $image = base64_encode($file);
    $image = base64_encode(file_get_contents($request->file('photo')));
    if (!($image)) {
      echo "<h3>Image null!</h3>";
    }
  }
} else {
  echo "<h3>Request doesn't have photo</h3>";
}
/*Try to upload values to database, return errors if fail */
try {
  $insert =
  DB::table('CCEAGpoc.dbo.Student')->insert([
    ['student_id'  => $data['student_id'],
    'forename'    => $data['forename'],
    'surname'     => $data['surname'],
    'course_id'   => $data['course_id'],
    'photo'       => $image,
    'status_id'   => $data['status_id']]
  ]);
  return view('success');
} catch (Exception $ex) { 
  return redirect()->back()->withInput($request->all())->withErrors($errors);
}

For full clarity, my form looks like the following:

<form action="submitAdd" method="post" class="form-inline" enctype="multipart/form-data">
  @csrf
  ...
  <div class="form-group">
      <label for="photo">Photo: </label>
        <input type="file" name="photo" />
  </div>

  <div class="form-group">
    <input type="submit" name="submit" value="Add Student" />
  </div>
</form>

I am honestly not sure what it is about the above that is causing the error. If anyone could help, I'd be hugely thankful.

Answer 1

You must decode your base64 string to binary prior to insert it onto varbinary field, as it will accept only binary data.

The base64 permits a safe binary file transmission, but it is not a valid binary value for mssql varbinary, as it is a string type.

DECLARE @string varchar(20)

SET @string = 'Hello World'

SELECT CONVERT(varbinary, @string)

Also, if your are actually using mssql, you should add mssql tag to your question accordingly, there a lot of people with a lot of mssql skills out there (mssql its not my typical day to day database, my knowledge of it is seriously limitated) that are missing your question.