Re: [sqlite] Escaped values throws error

[P Kishor]

On 11/16/08, Ben Marchbanks <[EMAIL PROTECTED]> wrote:
> Abridged sample of the insert statement that produces an error when
>  trying to insert escaped text.
>
>
>  INSERT INTO pageWords (word) VALUES (" \"nitch\" ");

SQL text strings are delimited by single quotes. If your really want
to insert "nitch" (including the double quotes), try ... VALUES
('"nitch"');

Alternatively, check out the ESCAPE clause in SQLite docs.


>
>  SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
>
>
>  Using
>
>  PHP Version 5.2.4
>  PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2
>  2007/03/23 14:30:00 wez Exp $
>  SQLite 3.4.2
>
>  --
>  *Ben Marchbanks*
>
>  www.magazooms.com 
>  Signature
>  Email: [EMAIL PROTECTED] 
>  Phone: (864) 284.9918
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Jay A. Kreibich]

On Sun, Nov 16, 2008 at 01:19:27PM -0500, Ben Marchbanks scratched on the wall:
> Abridged sample of the insert statement that produces an error when
> trying to insert escaped text.
> 
> 
> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");

  It is throwing an error because this is not valid SQL.

  In SQL, string literals use single quotes, so you want:

INSERT INTO pageWords (word) VALUES (' "nitch" ');

  If you're not familiar with SQL syntax, you might want to read this:

http://www.sqlite.org/lang_expr.html

  In specific, on string literals:

  A string constant is formed by enclosing the string in single
  quotes ('). A single quote within the string can be encoded by
  putting two single quotes in a row - as in Pascal. C-style
  escapes using the backslash character are not supported because
  they are not standard SQL.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[John Stanton]

An SQL literal uses single quotes.

Ben Marchbanks wrote:
> Abridged sample of the insert statement that produces an error when
> trying to insert escaped text.
> 
> 
> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
> 
> SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
> 
> 
> Using
> 
> PHP Version 5.2.4  
> PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 
> 2007/03/23 14:30:00 wez Exp $
> SQLite 3.4.2
> 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Ben Marchbanks]

Thanks - I did find a solution since I was not successful using 
sqlite_escape_string.

I think that the SQLite implementation in PHP via PDO is the culprit.

I wound up using double escape - str_replace("\"","\"\"",$value);


*Ben Marchbanks*

www.magazooms.com 
Signature
Email: [EMAIL PROTECTED] 
Phone: (864) 284.9918


P Kishor wrote:
> On 11/16/08, Ben Marchbanks <[EMAIL PROTECTED]> wrote:
>   
>> Abridged sample of the insert statement that produces an error when
>>  trying to insert escaped text.
>>
>>
>>  INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
>> 
>
> SQL text strings are delimited by single quotes. If your really want
> to insert "nitch" (including the double quotes), try ... VALUES
> ('"nitch"');
>
> Alternatively, check out the ESCAPE clause in SQLite docs.
>
>
>   
>>  SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
>>
>>
>>  Using
>>
>>  PHP Version 5.2.4
>>  PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2
>>  2007/03/23 14:30:00 wez Exp $
>>  SQLite 3.4.2
>>
>>  --
>>  *Ben Marchbanks*
>>
>>  www.magazooms.com 
>>  Signature
>>  Email: [EMAIL PROTECTED] 
>>  Phone: (864) 284.9918
>>  ___
>>  sqlite-users mailing list
>>  sqlite-users@sqlite.org
>>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Ben Marchbanks]

Hmmm - thanks for straightening me out guys - somehow I thought that 
SQLite used double quotes

*Ben Marchbanks*

www.magazooms.com 
Signature
Email: [EMAIL PROTECTED] 
Phone: (864) 284.9918


John Stanton wrote:
> An SQL literal uses single quotes.
>
> Ben Marchbanks wrote:
>   
>> Abridged sample of the insert statement that produces an error when
>> trying to insert escaped text.
>>
>>
>> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
>>
>> SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
>>
>>
>> Using
>>
>> PHP Version 5.2.4  
>> PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 
>> 2007/03/23 14:30:00 wez Exp $
>> SQLite 3.4.2
>>
>> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Ben Marchbanks]

Now I remember why I wasn't using single quotes in SQLite statements -

because escaped single quoted values like

'Billy\'s bike'

cause it to fail. Again I think its related to PHP-PDO SQLite 
implementation.


*Ben Marchbanks*

www.magazooms.com 
Signature
Email: [EMAIL PROTECTED] 
Phone: (864) 284.9918


Ben Marchbanks wrote:
> Hmmm - thanks for straightening me out guys - somehow I thought that 
> SQLite used double quotes
>
> *Ben Marchbanks*
>
> www.magazooms.com 
> Signature
> Email: [EMAIL PROTECTED] 
> Phone: (864) 284.9918
>
>
> John Stanton wrote:
>   
>> An SQL literal uses single quotes.
>>
>> Ben Marchbanks wrote:
>>   
>> 
>>> Abridged sample of the insert statement that produces an error when
>>> trying to insert escaped text.
>>>
>>>
>>> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
>>>
>>> SQLSTATE[HY000]: General error: 1 near "nitch": syntax error'
>>>
>>>
>>> Using
>>>
>>> PHP Version 5.2.4  
>>> PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 
>>> 2007/03/23 14:30:00 wez Exp $
>>> SQLite 3.4.2
>>>
>>> 
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>   
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Eric Minbiole]

> Now I remember why I wasn't using single quotes in SQLite statements -
> 
> because escaped single quoted values like
> 
> 'Billy\'s bike'
> 
> cause it to fail. Again I think its related to PHP-PDO SQLite 
> implementation.

Have you considered using prepared statements and bound parameters? 
That way, you never have to worry about character escaping, or SQL 
injection problems.

~Eric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Ben Marchbanks]

This is my first foree into PDO-SQLite and I saw samples using binding,
I am using prepare() though without binding.

So you think that had I used binding my escape issue would not be an issue ?

*Ben Marchbanks*

www.magazooms.com 
Signature
Email: [EMAIL PROTECTED] 
Phone: (864) 284.9918


Eric Minbiole wrote:
>> Now I remember why I wasn't using single quotes in SQLite statements -
>>
>> because escaped single quoted values like
>>
>> 'Billy\'s bike'
>>
>> cause it to fail. Again I think its related to PHP-PDO SQLite 
>> implementation.
>> 
>
> Have you considered using prepared statements and bound parameters? 
> That way, you never have to worry about character escaping, or SQL 
> injection problems.
>
> ~Eric
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Eric Minbiole]

> This is my first foree into PDO-SQLite and I saw samples using binding,
> I am using prepare() though without binding.
> 
> So you think that had I used binding my escape issue would not be an issue ?

Correct.  Though I've not used PDO-SQLite (I use the C interface), I 
would think that using bound parameters would eliminate your string 
escaping issues.  I use bound parameters almost exclusively:  You never 
have to worry about escaping or sanitizing your strings-- just let the 
Db engine do the work for you.

A related classic: http://xkcd.com/327/

~Eric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Igor Tandetnik]

"Ben Marchbanks" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> Now I remember why I wasn't using single quotes in SQLite statements -
>
> because escaped single quoted values like
>
> 'Billy\'s bike'
>
> cause it to fail.

You probably want 'Billy''s bike'. Backslashes don't have any special 
meaning in SQL.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[Dan]

On Nov 17, 2008, at 1:25 AM, P Kishor wrote:

> On 11/16/08, Ben Marchbanks <[EMAIL PROTECTED]> wrote:
>> Abridged sample of the insert statement that produces an error when
>> trying to insert escaped text.
>>
>>
>> INSERT INTO pageWords (word) VALUES (" \"nitch\" ");
>
> SQL text strings are delimited by single quotes. If your really want
> to insert "nitch" (including the double quotes), try ... VALUES
> ('"nitch"');
>
> Alternatively, check out the ESCAPE clause in SQLite docs.

The ESCAPE clause is part of the syntax for LIKE expressions. Despite
what it intuitively sounds like it should do, it cannot be used to  
escape
characters in string literals. The passage that describes how to form
string literals is hidden in this page:

   http://www.sqlite.org/lang_expr.html

It reads:

   "A string constant is formed by enclosing the string in single  
quotes (').
A single quote within the string can be encoded by putting two  
single
quotes in a row - as in Pascal. C-style escapes using the backslash
character are not supported because they are not standard SQL."

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Escaped values throws error

[John Stanton]

It is particularly valuable to use bound values when you have WWW access 
so that you block the possibility of SQL injection attacks.

Eric Minbiole wrote:
>>This is my first foree into PDO-SQLite and I saw samples using binding,
>>I am using prepare() though without binding.
>>
>>So you think that had I used binding my escape issue would not be an issue ?
> 
> 
> Correct.  Though I've not used PDO-SQLite (I use the C interface), I 
> would think that using bound parameters would eliminate your string 
> escaping issues.  I use bound parameters almost exclusively:  You never 
> have to worry about escaping or sanitizing your strings-- just let the 
> Db engine do the work for you.
> 
> A related classic: http://xkcd.com/327/
> 
> ~Eric
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users