Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!news.teledata-fn.de!newsfeed.arcor.de!newsspool4.arcor-online.net!news.arcor.de.POSTED!not-for-mail
Content-Type: text/plain; charset="UTF-8"
Message-ID: <5066304.ojPbGhBDv6@PointedEars.de>
From: Thomas 'PointedEars' Lahn <PointedEars@web.de>
Reply-To: Thomas 'PointedEars' Lahn <cljs@PointedEars.de>
Organization: PointedEars Software (PES)
Date: Wed, 13 Nov 2013 02:45:27 +0100
User-Agent: KNode/4.10.5
Content-Transfer-Encoding: 8Bit
X-Face: %i>XG-yXR'\"2P/C_aO%~;2o~?g0pPKmbOw^=NT`tprDEf++D.m7"}HW6.#=U:?2GGctkL,f89@H46O$ASoW&?s}.k+&.<b';Md8`dH6iqhT)6C^.Px|[=M@7=Ik[_w<%n1Up"LPQNu2m8|L!/3iby{-]A+#YE}Kl{Cw$\U!kD%K}\2jz"QQP6Uqr],./"?;=4v
Face: iVBORw0KGgoAAAANSUhEUgAAADAAAAAwBAMAAAClLOS0AAAAGFBMVEXTxa4RFk5dUWANED8PFEfy7+MGBiW+n3ZNF/QuAAACaElEQVQ4jVXUwVOcMBQG8Dc7Rc4PUntdWV2uxjDpGaGeozOp1woar4jd5t/v9wLstMwsA/ntlxdCAgUc1hjTc9/JCZfGoo3wG3HdmdAWrIJRHe7GM/TmpY5VFefuVcAkkPbLIaN8rmPmjloyZxgyR3GuJ4K0AGtJ2htz8o7yqikm759fldQXaMpbDzjKAG+8v+AugVTOPO5DOjLvGtUYQwh0CPjnVMyGd+8/GfUB5nLKJDD2aLDh5HYyMDJGDwQIo2ZmZcKbowNmAdB/AzyFhrmF2MHRb0QJJfaAnwGB6orZhoykLzJtGwF/xpYxI1dswomiUj3gTuAIqCn/4C7cULwGNBtwMTk3Y4LfKB5YUaOKBKYtpplm7u0vip8tU1NWWyI/7XdcSuIDoMt6rVHMWT0DbjHPGqDqZVSa6zleLcUTcIKLoMv3ueJluALtAo9B302zPPlrtiVScRdCjXvVh3e3JpYa/jjkuC9N+LrBMlz/eAN4eQijX2EdLo6c5tGGHwLyHFtXk89dDGHwCVhG9T0S/j55AhRZgkMCmUQXJ49TnS1wnQDvw0eAh9ICeMmEFbCnPMFzjAvsWoEWEFdYEx+S0MoUZ1gT1wId8+AF3Bl2OoEu906AUHx5VLw/gXYg/x84loOah/2UYNrgiwSwGO7RfUzVBbx/kgpckumGOi6QirtD6gkLTitbnxNol47S2jVc2vsN5kPqaAHT8uUdAJM4v/DanjYOwmUjWznGfwB7sGtAtor5BgofDuzaRj4kSQAqDakTsKORa3Q3xKi3gE1fhl71KRMqrdZ2AWNNg/YOhQyrVBnb+i+nEg4bsDA+egAAAABJRU5ErkJggg==
Subject: Re: JSON to access error
Newsgroups: comp.lang.javascript
References: <RIqdnX5TgYfBTR_PnZ2dnUVZ_vidnZ2d@westnet.com.au>
MIME-Version: 1.0
Lines: 48
NNTP-Posting-Date: 13 Nov 2013 02:45:29 CET
NNTP-Posting-Host: ff74c4ba.newsspool1.arcor-online.net
X-Trace: DXC=CL7D8]UHJ:G[6=1B@oB@@@ic==]BZ:afN4Fo<]lROoRAnkgeX?EC@@@g^WmZX^PGjCDZm8W4\YJNLYUVHKH;GI^G9K67=SAS@HJD^G`JOjScjA
X-Complaints-To: usenet-abuse@arcor.de
Xref: csiph.com comp.lang.javascript: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.