not able to upload file when using DB_CONTENT

nlooije

I would like to upload the file contents using DB_CONTENT directly into my SQL Server database.
However I am confused about the datatype required by that field, is it binary (varbinary) or string (nvarchar)?
The documentation on doing this is a little sparse in my opinion.

Regardless of if I use varbinary or nvarchar, if I upload any file, the server returns an error but without any description as if it just aborts. No errors in the console or PHP logs, nothing.

I have traced the point of the abort to line 687 in Upload.php; $res = $q->exec();.
If i var_dump a variable before and after this call it only shows the former but not the latter.

My guess is it has to do with line 627; $q->set($column, file_get_contents($upload['tmp_name']));
This reads the file contents into a string but it doesn't play well with uploading to the database maybe because it contains some bad characters.

If I use datatype nvarchar(max) for the field, and change line 627 to:
$q->set( $column, base64_encode(file_get_contents($upload['tmp_name'])) );
it uploads correctly.
If I do this with datatype varbinary, the insert fails (but atleast with an error message) because cannot implicitely convert varchar to varbinary without a CONVERT statement.

so the question is, what is the correct method to use DB_CONTENT?

allan

The PHP documentation states that file_get_contents is binary safe. I would have expected varbinary to be the correct data type to use.

What we really need is the error message with the rejection from the server.

Have you tried adding:

error_reporting(\E_ALL);
ini_set('display_errors', '1');

at the top of the script with the Editor::inst(...) in it? That should hopefully get some error message into the response if it is coming from PHP. If it is coming from the SQL server it should be part of the JSON.

the server returns an error but without any description as if it just aborts

Is it a 500 error it is returning?

Allan

nlooije

I tried adding the error reporting, still no errors are shown other than: A server error occurred while uploading the file.

I see a couple of posts suggesting using bin2hex() and decbin() but that would just return another string which wouldn't upload into a varbinary field without a CONVERT.

So far only been able to do this using base64_encode()

nlooije

.

allan

So the fact that the response from the server is empty is why you are getting the "A server error occurred" message. Editor always expects valid JSON.

I don't know why that is happening though - I had thought that all code paths for Editor would result in a valid response, or error text if something fails!

It does sound like you'll need to work one of your workarounds at the moment I'm afraid. I'll try to reproduce the error here.

Allan

nlooije

Ok sounds good, I will keep the base64_encode() workaround for now.

nlooije

I tried some more debugging.

The binary data upload fails to provide meaningful error message for me, except if data() is called and the result shown using var_dump.
Then it shows a 102 SQL syntax error and a malformed JSON return likely
because it can't interpret the binary unless DB is in UTF8 collation.
As I am using a SQL Server 2014 version, which does not support UTF8, this upload fails.

$d = $editor->process($_POST)->data();
ini_set('xdebug.var_display_max_depth', 99);
var_dump($d);
var_dump($_FILES);

allan

Ahh! That's fantastic debugging - thank you! I'm not sure I'd ever have found that since we use SQL Server 2022 for our testing at the moment.

It sounds like the base64 encode is going to be the way to go for you if you need to store the file data in the db.

Allan