Re: [sqlite] Single quotes are causing misery

Eugene Wee Thu, 17 Mar 2005 06:13:42 -0800

I doubt that's the problem though, since the problem pertains to content inserted, not the identifiers used. My guess is that PHP is being used, and that my earlier suggestion should fix it.

Eugene Wee

[EMAIL PROTECTED] wrote:

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?

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

Reply via email to