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 (user@example.com, 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 (user@example.com, 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, user@example.com)

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.

The first attempt is correct EXCEPT the array needs to be outside of the question marks. `DB::raw("CONVERT(binary, ?)", [$username])` — aynber, Jul 14 '20 at 18:02
@aynber yes sorry, it was just a typo, im gonna edit the question now and that's exactly what my code really looks like, still does not work. — Ronnel Martinez, Jul 14 '20 at 18:03
It looks like there's a few issues with the data. For ID, you appear to be passing in `CONVERT(binary, 2011-11-11 00:00:00)`, and password is `CONVERT(binary, 0)`. — aynber, Jul 14 '20 at 18:19
Im actually trying to figure it out why this happen still can't find the right way. — Ronnel Martinez, Jul 14 '20 at 18:46
I actually attempt to put everything in raw or I basically entered everything in query without variables but I still get this error: `String or binary data would be truncated.` — Ronnel Martinez, Jul 14 '20 at 18:47
You might want to compare the values you're entering with the columns that you're trying to insert them into. Take your raw query and try it directly into your database. — aynber, Jul 14 '20 at 19:25
Your last query error says: "String or binary data would be truncated" is the field big enough to hold the data? — Robbin Benard, Jul 17 '20 at 09:32
Could you please check the post - https://stackoverflow.com/questions/20864872/how-to-bind-parameters-to-a-raw-db-query-in-laravel-thats-used-on-a-model — Dmitry Leiko, Jul 17 '20 at 09:52
What is the datatype of the `id` column? (And the other columns too, but I suspect id is an int? Columns called "id" are usually ints) — allmhuran, Jul 17 '20 at 13:39
@allmhuran actually the system is so old that their id is actually a string, a unique string, basically the id indicated to their DB is actually the "username" that's why I coded it like that. — Ronnel Martinez, Jul 17 '20 at 16:45
Fair enough, so it's a `varchar`... but a `varchar` of what length? `create table tbl_refaccount (id varchar(what goes here?), ...)`. We really need to see the table definition for `tbl_rfaccount`, ie, the column datatypes. — allmhuran, Jul 17 '20 at 16:48
@allmhuran sorry, upon checking, it is actually binary(16). The example data stored in db is `0x6875776172616E673031000000` — Ronnel Martinez, Jul 17 '20 at 17:21
And the username value you are trying to insert is not longer than 16 characters? Similar question for password. — allmhuran, Jul 17 '20 at 17:24

Vladimir Baranov · Accepted Answer · 2020-07-19T02:23:42.493

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, user@example.com)

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.

The [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#arguments) states, that the _length_ is an _optional integer that specifies the length of the target data type, for data types that allow a user specified length_ and that the _default value is 30_. But specifying the length is a good habit. — Zhorov, Jul 18 '20 at 17:15
This answer actually works (I actually set it from binary(16) to binary(13) btw) but I am having one more problem. The inserted data using that method is different than the right one, I am getting this (screenshot): https://imgur.com/a/b1HoxHU, all of the accounts listed to that screenshot works except to the row with id `asdasd0` and password `asdasd0`. the row with the id `asdasd0` is inserted using the answer that you provided. — Ronnel Martinez, Jul 18 '20 at 17:34
@Zhorov, thank you. You are correct about the behaviour of `CONVERT` function. I have updated the answer. — Vladimir Baranov, Jul 19 '20 at 02:24
@RonnelMartinez, sorry, I don't understand what kind of problem you have when I look at your screenshot. I recommend you to ask another question that would be focused on that specific problem that the data that is inserted is not what you expect. You'll need to provide an example of your code and values, the definition of your table, what you expect and what you actually get. — Vladimir Baranov, Jul 19 '20 at 02:29
Regardless, thanks for the answer. That actually solves my problem anyway. — Ronnel Martinez, Jul 19 '20 at 09:19

Tharindu Vindula · Answer 2 · 2020-07-18T17:55:57.047

1

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
    ]);

edited Jul 18 '20 at 17:55

answered Jul 17 '20 at 07:33

Tharindu Vindula

1,192
1
8
18

Not working, I'm pretty sure I did not miss any comma or what but I am getting this error: ` SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ','. (SQL: insert into [dbo].[tbl_rfaccount] ([Email], [id], [password], [birthdate], [accounttype], [BCodeTU]) values ('example@example.com', , , '2011-11-11 00:00:00', 0, 1)) ` – Ronnel Martinez Jul 17 '20 at 08:58
first check your $username and $password using consoling – Tharindu Vindula Jul 17 '20 at 20:26
I just did it. Both `$username` and `$password` have values before the `DB::raw()` – Ronnel Martinez Jul 17 '20 at 21:10
then use DB::raw(CONVERT($username USING BINARY)) – Tharindu Vindula Jul 17 '20 at 21:21
I get: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'exampleUsername'. (SQL: insert into [dbo].[tbl_rfaccount] ([Email], [id], [password], [birthdate], [accounttype], [BCodeTU]) values (example@outlook.com, CONVERT('exampleUsername' USING BINARY), CONVERT('exampleUsername' USING BINARY), 1900-01-01 00:00:00, 0, 1)) – Ronnel Martinez Jul 17 '20 at 21:28

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

2 Answers2