thinking through parsing multi-line database commands with cr in the data

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

thinking through parsing multi-line database commands with cr in the data

Dr. Hawkins
I'm still kind of thinking aloud on this, and don't want to shoot my foot
clean off.

I'm developing with the ipad version in mind for the future, too.

My preferred db will be postgres, with mysql as an alternate (if I don't
keep tripping across it's "whoops!"s).  However, for performance reasons
(as well as the demo & single use versions working without having to
install a db server!), it will be necessary to support sqlite as well,
keeping the local db.

I'm writting a wrapper to take multiple commands, and possibly an
update/sync command, which could be to the table in the same or another db.

If I'm feeding postgres/mysql, I can have a begin/update this/insert
that/commit sequence, no problem.

SQLite, though, seems to have at least sporadic issues with multi-line
command blocks, and may need them sent one at a time.

What I'm scratching my brain for is "unpeeling" a multi-command block where
the data will usually include cr.

All I'm coming up with so far is putting a line in, and testing for things
like "INSERT INTO" "UPDATE" and any other command I use on the next line to
see if it is indeed a new line, and otherwise appending to the current
working line.

But this will trip up on, for example, an address like:

   Joe Scmoe, President
   Update your mac!
   123 E. Main St.


Or am I (hopefully) missing an obvious solution (other than dump sqlite :)


--
Dr. Richard E. Hawkins, Esq.
(702) 508-8462
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: thinking through parsing multi-line database commands with cr in the data

Dave Probert
Hi,

I found that the simplest route for me when working with multi-line field data was to pre-parse the data on storage and post parse it on extraction, by changing the 'cr''s to either another seldom used character or html (eg. '<br>').

So the database code before the INSERT/UPDATE would replace and cr's with '<br>', and once a SELECT is done either a single function to replace the '<br>'s with cr in all the data returned (possibly multi row) or to do the replace at the usage of the individual row/field.

Doing it this way means that the database manager (Postgres/Mysql/Sqlite) doesn't really know or care about multi-line data.  But the code handling the data should be carefully constructed to make sure that there are no instances of the data getting through to the end user (view OR edit) with the replaceable characters in.

Obviously if you are using actual html content then it's even easier - simply replace ALL cr's with empty on write.  And replace '<br>' and '</p>'s with the same AND a cr (for editing use).

The whole process comes down to a couple of lines of code in the end and making sure you document which fields you do a morphing of the data in - so that it's easier to remember in a years time ;) - if necessary!

Hope that helps a bit,
Dave
Reply | Threaded
Open this post in threaded view
|

Re: thinking through parsing multi-line database commands with cr in the data

Peter Haworth
In reply to this post by Dr. Hawkins
I'd be inclined to have a handler to execute/buffer the SQL statements
based on what type of db is in use.

For example you could have a handler dbCommand which took SQL statements as
a parameter.  If the DB is SQLite, the handler would execute the statement
immediately.  If some other version of SQL, it would buffer up the
statement in a global or script local.  You could use the BEGIN statement
to clear the buffer and the COMMIT statement to signify that the block of
statements was complete and should be executed.

As far as the issue of cr's in data, that's a problem whether you're
buffering commands or not.  I long since switched over to using the
"variableslist" option of the various rev database commands, usually with
an array rather than a variable, since it completely avoids the problem of
cr's, escaping quotes, etc.  And it protects against SQL injection which
would seem to be an important issue for you since you're handling sensitive
information over the internet.

Pete
lcSQL Software <http://www.lcsql.com>


On Sat, May 25, 2013 at 10:49 AM, Dr. Hawkins <[hidden email]> wrote:

> I'm still kind of thinking aloud on this, and don't want to shoot my foot
> clean off.
>
> I'm developing with the ipad version in mind for the future, too.
>
> My preferred db will be postgres, with mysql as an alternate (if I don't
> keep tripping across it's "whoops!"s).  However, for performance reasons
> (as well as the demo & single use versions working without having to
> install a db server!), it will be necessary to support sqlite as well,
> keeping the local db.
>
> I'm writting a wrapper to take multiple commands, and possibly an
> update/sync command, which could be to the table in the same or another db.
>
> If I'm feeding postgres/mysql, I can have a begin/update this/insert
> that/commit sequence, no problem.
>
> SQLite, though, seems to have at least sporadic issues with multi-line
> command blocks, and may need them sent one at a time.
>
> What I'm scratching my brain for is "unpeeling" a multi-command block where
> the data will usually include cr.
>
> All I'm coming up with so far is putting a line in, and testing for things
> like "INSERT INTO" "UPDATE" and any other command I use on the next line to
> see if it is indeed a new line, and otherwise appending to the current
> working line.
>
> But this will trip up on, for example, an address like:
>
>    Joe Scmoe, President
>    Update your mac!
>    123 E. Main St.
>
>
> Or am I (hopefully) missing an obvious solution (other than dump sqlite :)
>
>
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode