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

Groups > comp.lang.javascript > #21992

Re: JSON to access error

Show all headers | View raw

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.

Back to comp.lang.javascript | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web