1

I want to save an image to the database. I'm using PHP 5.3, Jquery and SQL Server. I'm using VARBINARY(MAX) field to store my file. But, it always returns "Incorrect syntax near" in my file string format.

I have read any solutions from stackoverflow, etc, but nothing worked.

This is my code 

<?php
include "../../system/conn.php";
$xuser_id = $_POST['xuser_id'];
$xusername = $_POST['xusername'];
$xpassword = md5($_POST['xpassword']);
$xrealname = $_POST['xrealname'];
$xgrup_id= $_POST['xgrup_id'];
$stat = $_POST['stat'];
$ttd = file_get_contents($_FILES['file']['tmp_name']);

$query = "EXECUTE [dbo].[sp_pengguna] '$xuser_id','$xusername','$xpassword','$xrealname','','','$xgrup_id','','','$stat','$ttd'";



$input = mssql_query($query) or die(mssql_get_last_message());

print_r($input);

?>

and this is my table table

and this is my function

ALTER PROCEDURE  [dbo].[sp_pengguna] 
    @user_id nvarchar(50), 
    @username nvarchar(20),
    @password nvarchar(50),
    @realname nvarchar(50),
    @last_login nvarchar(50),
    @last_login_ip nvarchar(50),
    @grup_id nvarchar(50),
    @input_date datetime,
    @last_update datetime,
    @stat varchar(20),
        @ttd varbinary(max)
AS 
    SET NOCOUNT ON;
    if @stat = 'delete' and @user_id<>''
        begin
            DELETE FROM [dbo].[pengguna]
                  WHERE [user_id] = @user_id
        end
    else
        begin   
        if @user_id='' 
            begin
                INSERT INTO [dbo].[pengguna]
                           ([user_id]
                           ,[username]
                           ,[password]
                           ,[realname]
                           ,[last_login]
                           ,[last_login_ip]
                           ,[grup_id]
                           ,[input_date]
                           ,[last_update]
                                ,[ttd_pengguna]
                                )
                     VALUES
                           (NEWID()
                           ,@username
                           ,@password
                           ,@realname
                           ,@last_login
                           ,@last_login_ip
                           ,@grup_id
                           ,GETDATE()
                           ,GETDATE()
                                ,@ttd
                           )
            end
        else 
            begin
                UPDATE [dbo].[pengguna]
                   SET [username]   = @username
                      ,[password]   = @password
                      ,[realname]   = @realname
                      ,[grup_id]    = @grup_id
                      ,[last_update]= @last_update
                        ,[ttd_pengguna] = @ttd
                 WHERE [user_id]    = @user_id
            end
    end;

when I'm trying to upload an image it always returns "Incorrect syntax near " this when trying upload PNG image enter image description here

this is when trying upload JPEG image enter image description here

I don't have any idea about this, anyone has solutions?

JuNas
  • 432
  • 2
  • 7
  • 16
  • 1
    If you wish to store the contents of the file in your MSSQL database column, you will need to wrap that content inside of a PHP function `bin2hex` first before passing it to the procedure and then inside of the procedure use `CONVERT(VARBINARY(MAX), @ContentOfFile)` before inserting it. And when you try to print the contents back from SQL use PHP function `hex2bin` – Igor Ilic Jul 11 '19 at 06:25
  • 1
    Look at https://stackoverflow.com/questions/41631765/php-sqlsrv-insert-read-blob-varbinary-field-from-database-example – Serg Jul 11 '19 at 06:27
  • @IgorIlic it's work. but I can't use the hex2bin function because I'm using PHP 5.3. I have read this https://stackoverflow.com/a/9727203/4369288 solution, but not working. – JuNas Jul 11 '19 at 07:06

1 Answers1

0

solved, I just convert the image to base64 string and in the procedure use parameter with varchar type (don't use nvarchar) and then convert it to varbinary

JuNas
  • 432
  • 2
  • 7
  • 16