Laravel binding parameter using insert() with convert() inside

Question

I am having a hard time binding my SQL query and I only have a few braincells left.

Basically, this code works but prone to SQL injection:

return DB::connection('sqlsrv_rfo_user')
    ->table('dbo.tbl_rfaccount')
    ->insert([
        'Email' => $email,
        'id' => DB::raw("CONVERT(binary, '$username')"),
        'password' => DB::raw("CONVERT(binary, '$password')"),
        'birthdate' => $birthday,
        'accounttype' => 0,
        'BCodeTU' => 1
    ]);

I am trying to figure out how I can bind these lines of code:

'id' => DB::raw("CONVERT(binary, '$username')"),
'password' => DB::raw("CONVERT(binary, '$password')"),

I did attempt this:

'id' => DB::raw("CONVERT(binary, ?)", [$username]),
'password' => DB::raw("CONVERT(binary, ?)", [$password]),

and got this error:

SQLSTATE[07002]: [Microsoft][ODBC Driver 13 for SQL Server]COUNT field incorrect or syntax error (SQL: insert into [dbo].[tbl_rfaccount] ([Email], [id], [password], [birthdate], [accounttype], [BCodeTU]) values ([email protected], CONVERT(binary, 2011-11-11 00:00:00), CONVERT(binary, 0), 1, ?, ?))

and this:

'id' => DB::raw("CONVERT(binary, :username)", ['username' => $username]),
'password' => DB::raw("CONVERT(binary, :password)", ['password' => $password]),

and got this error:

SQLSTATE[IMSSP]: An error occurred substituting the named parameters. (SQL: insert into [dbo].[tbl_rfaccount] ([Email], [id], [password], [birthdate], [accounttype], [BCodeTU]) values ([email protected], CONVERT(binary, :username), CONVERT(binary, :password), 2011-11-11 00:00:00, 0, 1))

And if I try the full raw:

return DB::connection('sqlsrv_rfo_user')
        ->insert("
        INSERT INTO [dbo].[tbl_rfaccount]
            ([id]
            ,[password]
            ,[accounttype]
            ,[birthdate]
            ,[BCodeTU]
            ,[Email])
        VALUES
        ((CONVERT(binary, ?)), (CONVERT(binary, ?)), ?, ?, ?, ?)
    ", [$username, $password, 0, $birthday, 1, $email]);

I get this error:

SQLSTATE[22001]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]String or binary data would be truncated. (SQL: INSERT INTO [dbo].[tbl_rfaccount] ([id] ,[password] ,[accounttype] ,[birthdate] ,[BCodeTU] ,[Email]) VALUES ((CONVERT(binary, user01)), (CONVERT(binary, password01)), 0, 2011-11-11 00:00:00, 1, [email protected])

I've been using Eloquent since the time I started learning Laravel but I have a project that forces me to do these way of coding, so I have no choice.

Answer 1

Based on your last error message, when you are trying the full raw query:

SQLSTATE[22001]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]String or binary data would be truncated. (SQL: INSERT INTO [dbo].[tbl_rfaccount] ([id] ,[password] ,[accounttype] ,[birthdate] ,[BCodeTU] ,[Email]) VALUES ((CONVERT(binary, user01)), (CONVERT(binary, password01)), 0, 2011-11-11 00:00:00, 1, [email protected])

You need to specify the length of fields in CONVERT function.

Not CONVERT(binary, user01), but CONVERT(binary(16), user01). Specify the same length as your column is defined in the target table.

If you do not specify the length, then in some cases it is assumed to be 1 and in some 30.

Aaron Bertrand wrote a detailed article about this (and other) bad habits: Bad habits to kick : declaring VARCHAR without (length). varchar or binary or varbinary is similar here.

As @Zhorov correctly pointed out in the comment the CONVERT function assumes that length is 30 if it is not specified.

-- CONVERT Syntax:  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

...

length

An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The default value is 30.

Here is a simple example that demonstrates what is going on:

SELECT
    CONVERT(binary(16), '1234567890123456') AS Bin16
    ,CONVERT(binary, '1234567890123456') as BinNoLength
;

The result:

+------------------------------------+----------------------------------------------------------------+
| Bin16                              | BinNoLength                                                    |
+------------------------------------+----------------------------------------------------------------+
| 0x31323334353637383930313233343536 | 0x313233343536373839303132333435360000000000000000000000000000 |
+------------------------------------+----------------------------------------------------------------+

So, when you don't specify the length in CONVERT, you'll get the binary(30) result.

And when you try to insert this long value into the column in your table, your column is not long enough to store it, so the long value is truncated and you see this error message.

Answer 2

can you try this

return DB::connection('sqlsrv_rfo_user')
    ->table('dbo.tbl_rfaccount')
    ->insert([
        'Email' => $email,
        'id' => DB::raw(`CONVERT(binary,$username)`),
        'password' => DB::raw(`CONVERT(binary,$password)`),
        'birthdate' => $birthday,
        'accounttype' => 0,
        'BCodeTU' => 1
    ]);