Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.javascript > #21991 > unrolled thread

JSON to access error

Back to article view | Back to comp.lang.javascript

Contents

  JSON to access error Andrew Poulos <ap_prog@hotmail.com> - 2013-11-13 12:04 +1100
    Re: JSON to access error Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-11-13 02:45 +0100
      Re: JSON to access error "Michael Haufe (TNO)" <tno@thenewobjective.com> - 2013-11-12 19:05 -0800
    Re: JSON to access error Denis McMahon <denismfmcmahon@gmail.com> - 2013-11-14 00:56 +0000

#21991 — JSON to access error

I'm trying to update an MS Access db using ASP and JScript with this 
SQL. The value for "notes" is a JSON string posted via AJAX.

UPDATE tbl_Responses
SET notes=
'{
     "loc": "",
     "suspend_data": "{'assess':{},'limit':1,'loc':'','progress':'1'}",
     "comments": "",
     "completion": "completed",
     "scoEntry": "resume",
     "userID": 11,
     "lessonID": 2
}'
WHERE ResponseID=67;

Running it gives the error
Syntax error (missing operator) in query expression "{...

If I test the JSON in a JSON validator it checks out as valid.

If I delete the "suspend_data" line the SQL runs as expected.

Why is the "suspend_data" line causing the error?

Andrew Poulos

[toc] | [next] | [standalone]

#21992

Andrew Poulos wrote:

> I'm trying to update an MS Access db using ASP and JScript with this
> SQL. The value for "notes" is a JSON string posted via AJAX.
> 
> UPDATE tbl_Responses
> SET notes=
> '{
>      "loc": "",
>      "suspend_data": "{'assess':{},'limit':1,'loc':'','progress':'1'}",
>      "comments": "",
>      "completion": "completed",
>      "scoEntry": "resume",
>      "userID": 11,
>      "lessonID": 2
> }'
> WHERE ResponseID=67;

You should not store JSON in the database.  You should store the JSON data 
in properties of your model object which then generates the database query 
(by use of a database adapter so that you only have to change the adapter 
when you change the database [which is going to happen with an Access 
database sooner or later]).

> Running it gives the error
> Syntax error (missing operator) in query expression "{...
> 
> If I test the JSON in a JSON validator it checks out as valid.
> 
> If I delete the "suspend_data" line the SQL runs as expected.
> 
> Why is the "suspend_data" line causing the error?

It contains a “'” which is the SQL string delimiter here.

As you have been told before, you need to sanitize your input.

See also:

<http://xkcd.com/327/>
<http://en.wikipedia.org/wiki/Secure_input_and_output_handling>
<https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet>

-- 
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#21993

On Tuesday, November 12, 2013 7:45:27 PM UTC-6, Thomas 'PointedEars' Lahn wrote:
> 
> You should not store JSON in the database.  You should store the JSON data 
> in properties of your model object which then generates the database query 
> (by use of a database adapter so that you only have to change the adapter 
> when you change the database [which is going to happen with an Access 
> database sooner or later]).

+10

[toc] | [prev] | [next] | [standalone]

#22001

On Wed, 13 Nov 2013 12:04:34 +1100, Andrew Poulos wrote:

> I'm trying to update an MS Access db using ASP and JScript with this
> SQL. The value for "notes" is a JSON string posted via AJAX.
> 
> UPDATE tbl_Responses SET notes=
> '{
>      "loc": "",
>      "suspend_data": "{'assess':{},'limit':1,'loc':'','progress':'1'}",
>      "comments": "",
>      "completion": "completed",
>      "scoEntry": "resume",
>      "userID": 11,
>      "lessonID": 2
> }'
> WHERE ResponseID=67;

> Why is the "suspend_data" line causing the error?

Because the suspend data line is inside a single quoted string, and it 
contains single quotes, the first one of which terminates the single 
quoted string.

So:

UPDATE tbl_Responses SET notes = '{ "loc": "", "suspend_data": "{'

assess <-- I guess this is the failure point, because the sql doesn't 
recognise the word assess in this position as any sort of expected 
keyword / value / function.

But if assess was a valid access sql function, the next problem is no () 
containing an argument list, even an empty one, after it.

After assess, you have the following (as far as ms access sql is 
concerned series of apparent tokens and strings:

':{},'
limit
':1,'
loc
':'
','
progress
':'
1

and then a nice long string:

'}", "comments": "", "completion": "completed", "scoEntry": "resume", 
"userID": 11, "lessonID": 2 }'

followed by a where clause:

WHERE ResponseID=67;

Which altogether amount to something that the sql parser is turning it's 
nose up at because it doesn't fit the expected form of any sql statement 
the parser knows how to process.

As others have already said, you should probably convert the json object 
into some normalised data form and store that data in the database, 
rather than the json string.

You may need multiple tables depending on (a) whether you can have 
multiple different notes.suspend_data records associated with the same 
notes record, and (b) whether you can have multiple 
notes.suspend_data.assess records associated with the same 
notes.suspend_data record.

However that's getting into database normalization, which is probably 
going way off topic here.

Note that it may be possible to do the following, but it's not guaranteed 
to work:

sql = "UPDATE tbl_Responses SET notes = '{" 
      + Replace( json_string, "'", "''" ) 
      + "}' WHERE ResponseID=67;";

Which should replace all the ' inside the json string with '', which, if 
various web resources are to believed, will stop them being seen as sql 
string delimeters.

Not sure if you need the {} wrapper round the json string in there or 
not, or whether your json string already contains it, and this assumes 
that your asp is written in visual basic.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.javascript

csiph-web