Re: [sqlite] Single quotes are causing misery
On Thu 17 Mar 05, 4:10 PM, Reid Thompson <[EMAIL PROTECTED]> said: > > > I read that in a NSP book awhile ago (Linux Cookbook). It's > > faster than "%s/ //g". Very handy. But which problem does this > > address? :) > > > > Pete > > This is some PHP code on Linux. I suspect it was originally written > > > on a Microsoft operating system because when I edit the files, my > > > editor reports the textfiles as being "dos" (they contain > > > carriage-return and linefeeds at the end of each line). > > reid Oh, I was just telling what platform this was executing on. The problem was with quoting, not with what the end of line for textfiles is. I don't think that the internal format for the sourcecode textfile is the problem here. The problem was with unescaped single quotes confusing sqlite. Sorry for the confusion! Peter -- Save Star Trek Enterprise from extinction: http://www.saveenterprise.com GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
RE: [sqlite] Single quotes are causing misery
> I read that in a NSP book awhile ago (Linux Cookbook). It's > faster than "%s/ //g". Very handy. But which problem does this > address? :) > > Pete This is some PHP code on Linux. I suspect it was originally written > > on a Microsoft operating system because when I edit the files, my > > editor reports the textfiles as being "dos" (they contain > > carriage-return and linefeeds at the end of each line). reid
Re: [sqlite] Single quotes are causing misery
On Thu 17 Mar 05, 2:26 PM, Reid Thompson <[EMAIL PROTECTED]> said:
> Peter Jay Salzman wrote:
> > Hi Mike,
> >
> > To be perfectly honest, other than being a Microsoft thing, I
> > don't really know what .NET is. Pretty pathetic, huh? :)
> >
> > This is some PHP code on Linux. I suspect it was originally
> > written on a Microsoft operating system because when I edit
> > the files, my editor reports the textfiles as being "dos"
> > (they contain carriage-return and linefeeds at the end of each line).
> >
> > I hear you about the []; I *wish* I could use them. Proper
> > quoting inside of PHP is very painful:
> >
> >
> >$query = "INSERT INTO $database_table
> > (id, day, month, date, year, category, title, body,
> > showpref) VALUES (null,
> > '" . sqlite_escape_string($_POST['the_day']) . "',
> > '" . sqlite_escape_string($_POST['the_month']). "',
> > '" . sqlite_escape_string($_POST['the_date']) . "',
> > (snip)
> >
> > The stuff that looks quoted (the middle section) is actually
> > the stuff outside the language quotes, but inside the quoted
> > quotes. Gruesome.
> >
> > But if I don't use sqlite_escape_string, single quotes cause
> > a "SQL logic or missing database" error.
> >
> > But then, if I use sqlite_escape_string, I have to test
> > get_magic_quotes_gpc and use stripslashes, and Eugene
> > recommended. Hard to believe there isn't a better way of doing this!
> >
> > Pete
> >
> >
> >
> > On Thu 17 Mar 05, 5:59 AM,
> > [EMAIL PROTECTED]
> > <[EMAIL PROTECTED]> said:
> >> Are you using the SQLite .NET provider? Just curious, anyway, SQLite
> >> also supports using [ ] instead of " " and believe me it's a good
> >> thing, using " " as delimiters is a poor choice considering this
> >> conflicts with almost all languages when it comes to string
> >> concatenation. In fact, I recommend use [ ] over " " all of the time,
> >> however, the SQLite .NET managed driver has issues with the [ ]
> >> delimiter style.
> >>
> >>
> >>> I've nearly completed converting Wheatblog to sqlite. It's been
> >>> quite a learning experience! I've come across a problem I haven't
> >>> been able to figure out, though.
> >>>
> >>> Whenever I made a blog post that had a forward quote character (')
> >>> in either the title or the body of the post, I'd get an error.
> >>>
> >>> After a little Googling, I changed my query to:
> >>>
> >>>
> >>> $query = "INSERT INTO $database_table
> >>> (id, day, month, date, year, category, title, body,
> >>> showpref) VALUES (null, '" .
> >>> sqlite_escape_string($_POST['the_day']) . "', '" .
> >>> sqlite_escape_string($_POST['the_month']). "', '" .
> >>> sqlite_escape_string($_POST['the_date']) . "', '" .
> >>> sqlite_escape_string($_POST['the_year']) . "', '" .
> >>> sqlite_escape_string($_POST['the_category']) . "', '" .
> >>> sqlite_escape_string($_POST['the_title']). "', '" .
> >>> sqlite_escape_string($_POST['the_body']) . "', '" .
> >>> sqlite_escape_string($_POST['the_showpref']) . "')";
> >>>
> >>> DB_query($query, $db);
> >>>
> >>> and the definition of DB_query is:
> >>>
> >>>
> >>>function DB_query($cmd, $db)
> >>>{
> >>> $retval = sqlite_query($db, "$cmd")
> >>> or die('Query Error: ' .
> >>> sqlite_error_string(sqlite_last_error($db)));
> >>>
> >>> return $retval;
> >>>}
> >>>
> >>> This works in the sense that forward quotes no longer generate an
> >>> error. However, whenever I print out a blog post, the forward
> >>> quotes are all escaped. So if I post:
> >>>
> >>>This contains a ' character.
> >>>
> >>> The post, when printed looks like:
> >>>
> >>>This contains a \' character.
> >>>
> >>> What's the proper way to ensure that ' characters are properly
> >>> quoted but don't show up in the output?
>
> dos2unix "filename" will remove the trailing carriage returns
>
> reid
I read that in a NSP book awhile ago (Linux Cookbook). It's faster than
"%s/
//g". Very handy. But which problem does this address? :)
Pete
--
Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
RE: [sqlite] Single quotes are causing misery
Peter Jay Salzman wrote:
> Hi Mike,
>
> To be perfectly honest, other than being a Microsoft thing, I
> don't really know what .NET is. Pretty pathetic, huh? :)
>
> This is some PHP code on Linux. I suspect it was originally
> written on a Microsoft operating system because when I edit
> the files, my editor reports the textfiles as being "dos"
> (they contain carriage-return and linefeeds at the end of each line).
>
> I hear you about the []; I *wish* I could use them. Proper
> quoting inside of PHP is very painful:
>
>
>$query = "INSERT INTO $database_table
> (id, day, month, date, year, category, title, body,
> showpref) VALUES (null,
> '" . sqlite_escape_string($_POST['the_day']) . "',
> '" . sqlite_escape_string($_POST['the_month']). "',
> '" . sqlite_escape_string($_POST['the_date']) . "',
> (snip)
>
> The stuff that looks quoted (the middle section) is actually
> the stuff outside the language quotes, but inside the quoted
> quotes. Gruesome.
>
> But if I don't use sqlite_escape_string, single quotes cause
> a "SQL logic or missing database" error.
>
> But then, if I use sqlite_escape_string, I have to test
> get_magic_quotes_gpc and use stripslashes, and Eugene
> recommended. Hard to believe there isn't a better way of doing this!
>
> Pete
>
>
>
> On Thu 17 Mar 05, 5:59 AM,
> [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> said:
>> Are you using the SQLite .NET provider? Just curious, anyway, SQLite
>> also supports using [ ] instead of " " and believe me it's a good
>> thing, using " " as delimiters is a poor choice considering this
>> conflicts with almost all languages when it comes to string
>> concatenation. In fact, I recommend use [ ] over " " all of the time,
>> however, the SQLite .NET managed driver has issues with the [ ]
>> delimiter style.
>>
>>
>>> I've nearly completed converting Wheatblog to sqlite. It's been
>>> quite a learning experience! I've come across a problem I haven't
>>> been able to figure out, though.
>>>
>>> Whenever I made a blog post that had a forward quote character (')
>>> in either the title or the body of the post, I'd get an error.
>>>
>>> After a little Googling, I changed my query to:
>>>
>>>
>>> $query = "INSERT INTO $database_table
>>> (id, day, month, date, year, category, title, body,
>>> showpref) VALUES (null, '" .
>>> sqlite_escape_string($_POST['the_day']) . "', '" .
>>> sqlite_escape_string($_POST['the_month']). "', '" .
>>> sqlite_escape_string($_POST['the_date']) . "', '" .
>>> sqlite_escape_string($_POST['the_year']) . "', '" .
>>> sqlite_escape_string($_POST['the_category']) . "', '" .
>>> sqlite_escape_string($_POST['the_title']). "', '" .
>>> sqlite_escape_string($_POST['the_body']) . "', '" .
>>> sqlite_escape_string($_POST['the_showpref']) . "')";
>>>
>>> DB_query($query, $db);
>>>
>>> and the definition of DB_query is:
>>>
>>>
>>>function DB_query($cmd, $db)
>>>{
>>> $retval = sqlite_query($db, "$cmd")
>>> or die('Query Error: ' .
>>> sqlite_error_string(sqlite_last_error($db)));
>>>
>>> return $retval;
>>>}
>>>
>>> This works in the sense that forward quotes no longer generate an
>>> error. However, whenever I print out a blog post, the forward
>>> quotes are all escaped. So if I post:
>>>
>>>This contains a ' character.
>>>
>>> The post, when printed looks like:
>>>
>>>This contains a \' character.
>>>
>>> What's the proper way to ensure that ' characters are properly
>>> quoted but don't show up in the output?
dos2unix "filename" will remove the trailing carriage returns
reid
Re: [sqlite] Single quotes are causing misery
Well the [ ] aren't a .NET thing, SQLite supports them, using [ ] are a standard supported SQLite feature and they make you're life way, way, easier, see: http://www.sqlite.org/lang_keywords.html [keyword] A keyword enclosed in square brackets is always understood as an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility. While this might not be "standard SQL" using double quotes " as the special character is a very poor choice.
Re: [sqlite] Single quotes are causing misery
Hi Eugene,
Yes, this worked great. I just find it hard to believe that it's all
necessary.
Without using sqlite_escape_string, single quotes cause "SQL Logic or
missing database" errors. So I'm forced to use that function on variables
set via a form.
But then to avoid the "backslash in the data" problem, I need to use
stripslashes on the variables I'm about to write to the database.
But just in case a user has magic_quotes_gpc set off, I need to test that
function and then decide whether to use stripslashes() or not.
Problem solved, but the solution is kind of, well, "icky". I love the idea
of a RDBMS that doesn't require a daemon. And I love PHP. They're both so
convenient. But the difficulty of programming with the two taken together
is more than the sum of the "difficultness" of the two individually. :(
Thanks!
Pete
On Thu 17 Mar 05, 1:58 PM, Eugene Wee <[EMAIL PROTECTED]> said:
> Hi,
>
> I think the reason is that sqlite_escape_string() doubles single quotes
> to escape them.
> However, you have magic_quotes_gpc set to 1 in php.ini
> As such, incoming variables are escaped using backslashes.
>
> A solution is to use stripslashes() on the incoming variables if
> get_magic_quotes_gpc() returns 1, since you cant change magic_quotes_gpc
> at runtime.
> Alternatively, you can alter php.ini, but that's usually not practical.
>
> Eugene Wee
>
> Peter Jay Salzman wrote:
> >I've nearly completed converting Wheatblog to sqlite. It's been quite a
> >learning experience! I've come across a problem I haven't been able to
> >figure out, though.
> >
> >Whenever I made a blog post that had a forward quote character (') in
> >either
> >the title or the body of the post, I'd get an error.
> >
> >After a little Googling, I changed my query to:
> >
> >
> > $query = "INSERT INTO $database_table
> > (id, day, month, date, year, category, title, body, showpref)
> > VALUES (null,
> > '" . sqlite_escape_string($_POST['the_day']) . "',
> > '" . sqlite_escape_string($_POST['the_month']). "',
> > '" . sqlite_escape_string($_POST['the_date']) . "',
> > '" . sqlite_escape_string($_POST['the_year']) . "',
> > '" . sqlite_escape_string($_POST['the_category']) . "',
> > '" . sqlite_escape_string($_POST['the_title']). "',
> > '" . sqlite_escape_string($_POST['the_body']) . "',
> > '" . sqlite_escape_string($_POST['the_showpref']) . "')";
> >
> > DB_query($query, $db);
> >
> >and the definition of DB_query is:
> >
> >
> > function DB_query($cmd, $db)
> > {
> > $retval = sqlite_query($db, "$cmd")
> > or die('Query Error: ' .
> > sqlite_error_string(sqlite_last_error($db)));
> >
> > return $retval;
> > }
> >
> >This works in the sense that forward quotes no longer generate an error.
> >However, whenever I print out a blog post, the forward quotes are all
> >escaped. So if I post:
> >
> > This contains a ' character.
> >
> >The post, when printed looks like:
> >
> > This contains a \' character.
> >
> >What's the proper way to ensure that ' characters are properly quoted but
> >don't show up in the output?
> >
> >Thanks!
> >Pete
> >
>
--
Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
Re: [sqlite] Single quotes are causing misery
Hi Mike,
To be perfectly honest, other than being a Microsoft thing, I don't really
know what .NET is. Pretty pathetic, huh? :)
This is some PHP code on Linux. I suspect it was originally written on a
Microsoft operating system because when I edit the files, my editor reports
the textfiles as being "dos" (they contain carriage-return and linefeeds at
the end of each line).
I hear you about the []; I *wish* I could use them. Proper quoting inside
of PHP is very painful:
$query = "INSERT INTO $database_table
(id, day, month, date, year, category, title, body, showpref)
VALUES (null,
'" . sqlite_escape_string($_POST['the_day']) . "',
'" . sqlite_escape_string($_POST['the_month']). "',
'" . sqlite_escape_string($_POST['the_date']) . "',
(snip)
The stuff that looks quoted (the middle section) is actually the stuff
outside the language quotes, but inside the quoted quotes. Gruesome.
But if I don't use sqlite_escape_string, single quotes cause a "SQL logic or
missing database" error.
But then, if I use sqlite_escape_string, I have to test get_magic_quotes_gpc
and use stripslashes, and Eugene recommended. Hard to believe there isn't a
better way of doing this!
Pete
On Thu 17 Mar 05, 5:59 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> said:
> Are you using the SQLite .NET provider? Just curious, anyway, SQLite also
> supports using [ ] instead of " " and believe me it's a good thing, using
> " " as delimiters is a poor choice considering this conflicts with almost
> all languages when it comes to string concatenation. In fact, I recommend
> use [ ] over " " all of the time, however, the SQLite .NET managed driver
> has issues with the [ ] delimiter style.
>
>
> > I've nearly completed converting Wheatblog to sqlite. It's been quite a
> > learning experience! I've come across a problem I haven't been able to
> > figure out, though.
> >
> > Whenever I made a blog post that had a forward quote character (') in
> > either
> > the title or the body of the post, I'd get an error.
> >
> > After a little Googling, I changed my query to:
> >
> >
> > $query = "INSERT INTO $database_table
> > (id, day, month, date, year, category, title, body, showpref)
> > VALUES (null,
> > '" . sqlite_escape_string($_POST['the_day']) . "',
> > '" . sqlite_escape_string($_POST['the_month']). "',
> > '" . sqlite_escape_string($_POST['the_date']) . "',
> > '" . sqlite_escape_string($_POST['the_year']) . "',
> > '" . sqlite_escape_string($_POST['the_category']) . "',
> > '" . sqlite_escape_string($_POST['the_title']). "',
> > '" . sqlite_escape_string($_POST['the_body']) . "',
> > '" . sqlite_escape_string($_POST['the_showpref']) . "')";
> >
> > DB_query($query, $db);
> >
> > and the definition of DB_query is:
> >
> >
> >function DB_query($cmd, $db)
> >{
> > $retval = sqlite_query($db, "$cmd")
> > or die('Query Error: ' .
> > sqlite_error_string(sqlite_last_error($db)));
> >
> > return $retval;
> >}
> >
> > This works in the sense that forward quotes no longer generate an error.
> > However, whenever I print out a blog post, the forward quotes are all
> > escaped. So if I post:
> >
> >This contains a ' character.
> >
> > The post, when printed looks like:
> >
> >This contains a \' character.
> >
> > What's the proper way to ensure that ' characters are properly quoted but
> > don't show up in the output?
--
Save Star Trek Enterprise from extinction: http://www.saveenterprise.com
GPG Fingerprint: B9F1 6CF3 47C4 7CD8 D33E 70A9 A3B9 1945 67EA 951D
Re: [sqlite] Single quotes are causing misery
> What's the proper way to ensure that ' characters are properly quoted but > don't show up in the output? Honestly, we use the SQLite .NET managed driver and pass all data in via parameters, therefore we have no escape issues and more importantly no SQL injection woes, if you're taking data right off the string and passing it into SQLite I could probably enter some text that wipe out all of your data, consider parameters, they solve a whole bunch of problems.
Re: [sqlite] Single quotes are causing misery
Hi,
I think the reason is that sqlite_escape_string() doubles single quotes
to escape them.
However, you have magic_quotes_gpc set to 1 in php.ini
As such, incoming variables are escaped using backslashes.
A solution is to use stripslashes() on the incoming variables if
get_magic_quotes_gpc() returns 1, since you cant change magic_quotes_gpc
at runtime.
Alternatively, you can alter php.ini, but that's usually not practical.
Eugene Wee
Peter Jay Salzman wrote:
I've nearly completed converting Wheatblog to sqlite. It's been quite a
learning experience! I've come across a problem I haven't been able to
figure out, though.
Whenever I made a blog post that had a forward quote character (') in either
the title or the body of the post, I'd get an error.
After a little Googling, I changed my query to:
$query = "INSERT INTO $database_table
(id, day, month, date, year, category, title, body, showpref)
VALUES (null,
'" . sqlite_escape_string($_POST['the_day']) . "',
'" . sqlite_escape_string($_POST['the_month']). "',
'" . sqlite_escape_string($_POST['the_date']) . "',
'" . sqlite_escape_string($_POST['the_year']) . "',
'" . sqlite_escape_string($_POST['the_category']) . "',
'" . sqlite_escape_string($_POST['the_title']). "',
'" . sqlite_escape_string($_POST['the_body']) . "',
'" . sqlite_escape_string($_POST['the_showpref']) . "')";
DB_query($query, $db);
and the definition of DB_query is:
function DB_query($cmd, $db)
{
$retval = sqlite_query($db, "$cmd")
or die('Query Error: ' . sqlite_error_string(sqlite_last_error($db)));
return $retval;
}
This works in the sense that forward quotes no longer generate an error.
However, whenever I print out a blog post, the forward quotes are all
escaped. So if I post:
This contains a ' character.
The post, when printed looks like:
This contains a \' character.
What's the proper way to ensure that ' characters are properly quoted but
don't show up in the output?
Thanks!
Pete
