Error when using timestamp field

REST API
1

Hi,

I am new to the DreamFactory, and am testing the SQL API, I found that the system gives me an error whenever the contents of a field contain quotation marks. The error returned is:

The Response content must be a string or object implementing __toString()

Which to me means that when generating the response, the value of the field is just getting copied or evaluated without any sanity checking in order to convert quotes to some token that can be easily taken into account when displaying the response and or generating the JSON object. In my current application, I do take care of binding the field to the contents of a string that may contain quotes in order to allow this character. This is not getting done here, and therefore the result in my case is a 500 error, since the server cannot deal with the quotation marks from the field inside the response generation.

This issue is akin to being vulnerable to SQL injection. With the right value in a text field in the database, “bad things” can happen. Maybe this can be solved my using the addslashes() function??

I have tried to figure out where this is getting done, but have had no luck. Unless this is fixed, or a filter can be placed in order to “escape” these quotation marks inside the text fields, This is useless… :frowning:

2

Having trouble duplicating this issue. Can you provide more details?
What version of DreamFactory?
What operating system?
What type of SQL?
How is the data being stored?
How is the data being retrieved?

3

I’m also having this issue working against an MS SQL Server backend. Although I do not know if it is related to the data having quotes or not, since I haven’t been able to see any data.

My setup was a very simple install from docker, and the only thing I have done to the config was to add my DB source.

Edit: I was able to get data from other tables, so the DB connection seems to work. Other tables also exhibit this problem as well though.

  • DreamFactory 2.0
  • Linux/Docker container
  • MS SQL

Here is the full error:

4

I am not able to duplicate this problem are you on the latest version of DreamFactory? (2.0.2)

5

I’m using version 2.0.2

6

With more testing I was able to figure out that if I request the data in XML it works fine, so it’s something to do with JSON.

I also noticed that the tables that failed to get records were all tables that had a “RowVersion” field with type “timestamp”.

{ "name": "RowVersion", "label": "RowVersion", "description": null, "type": "timestamp", "db_type": "timestamp", "length": 8, "precision": null, "scale": null, "default": null, "required": true, "allow_null": false, "fixed_length": false, "supports_multibyte": false, "auto_increment": false, "is_primary_key": false, "is_unique": false, "is_index": false, "is_foreign_key": false, "is_virtual_foreign_key": false, "is_foreign_ref_service": false, "ref_service_id": null, "ref_table": null, "ref_fields": null, "ref_on_update": null, "ref_on_delete": null, "picklist": null, "validation": null, "db_function": null, "alias": null }

7

Yes, I was able to duplicate the problem that way. Actually had nothing to with quotes in the data.
I’ll look into the timestamp issue. Might need to file a bug.

8

I’m also experiencing this problem with one of my tables… I’m using DreamFactory Version: 2.0.2, Linux Debian, MariaDB (accessing remote database), the database it’s self is a Drupal7 DB and I’m having the problem when trying to access the ‘node’ table. All other tables I’ve tried seem to work OK.
I don’t have any fields type “timestamp”, but there are a couple of dates in there type “integer”.
Here’s the table scheme:
{
“name”: “node”,
“is_view”: false,
“label”: “Node”,
“plural”: “Nodes”,
“description”: null,
“alias”: null,
“primary_key”: “nid”,
“name_field”: null,
“field”: [
{
“name”: “nid”,
“label”: “Nid”,
“description”: null,
“type”: “id”,
“db_type”: “int(10) unsigned”,
“length”: 10,
“precision”: null,
“scale”: null,
“default”: null,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: true,
“is_primary_key”: true,
“is_unique”: false,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “vid”,
“label”: “Vid”,
“description”: null,
“type”: “integer”,
“db_type”: “int(10) unsigned”,
“length”: 10,
“precision”: null,
“scale”: null,
“default”: null,
“required”: false,
“allow_null”: true,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: true,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “type”,
“label”: “Type”,
“description”: null,
“type”: “string”,
“db_type”: “varchar(32)”,
“length”: 32,
“precision”: null,
“scale”: null,
“default”: “”,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: true,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “language”,
“label”: “Language”,
“description”: null,
“type”: “string”,
“db_type”: “varchar(12)”,
“length”: 12,
“precision”: null,
“scale”: null,
“default”: “”,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: true,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “title”,
“label”: “Title”,
“description”: null,
“type”: “string”,
“db_type”: “varchar(255)”,
“length”: 255,
“precision”: null,
“scale”: null,
“default”: “”,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: true,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “uid”,
“label”: “Uid”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “status”,
“label”: “Status”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 1,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “created”,
“label”: “Created”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “changed”,
“label”: “Changed”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “comment”,
“label”: “Comment”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “promote”,
“label”: “Promote”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “sticky”,
“label”: “Sticky”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “tnid”,
“label”: “Tnid”,
“description”: null,
“type”: “integer”,
“db_type”: “int(10) unsigned”,
“length”: 10,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
},
{
“name”: “translate”,
“label”: “Translate”,
“description”: null,
“type”: “integer”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: 0,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null,
“alias”: null
}
],
“related”: [],
“access”: 31
}

9

I fixed the issue I was experiencing but converting the db column to ASCII: CONVERT(title USING ASCII)
Seems it was a bad data error for me as the data has been migrated from a legacy db which has some encoding issues.

1 Like
10

I’m facing this problem too. Any updates on this?

Thanks!

Elvis

12

The issue only exists when using the ms sql timestamp data type with the dblib driver (the driver used for sql server on linux and mac systems.)
This is a filed bug and is being worked on. It will likely be resolved and released at the end of the month.

13

@formerstaff, thank you for your prompt response!

I’m using MySQL and I have a table with 5 columns (one of them is datetime, the others are integer and string) and around 2000 records. Using API docs to getRecords() from this table returns the error reported in the first message of this thread.

P.S.: investigating further I’ve noticed I can get this error even when using the Data menu option in DreamFactory. I listed the records in the table mentioned above and navigated through the pages. In a given page I faced the error again (image bellow). I managed to found that this error occurred because of a text column containing a character with accent (namely the word Taís - please note the symbol on top of letter i). After replacing í for i the error were gone.

That record were stored in MySQL via a REST service in an instance of DreamFactory running version 1.9. This error only occur in version 2 of DF.

I hope this detail helps on fixing the bug.

Thanks!

14

Thanks. That’s another issue we’re dealing with, and I wonder if they are related issues…
You can work around this in mysql by adding a charset parameter to your service connection string. See Problem it ö, ä, ü on MySQL

15

Awesome, that did the trick.

Thanks!