SQL Related Articles: Problems with binary memos in mySQL
2003-02-03 -- Steven Spierenburg
Newsgroups: softvelocity.products.c55ee
Hi Arnor,
If you don't use the field then there's no problem.
The moment you transfer such a field, be it through a File or View, then you
could have this potential problem.
Doesn't matter where or what, the moment this field has to be transfered
over the line you have to be aware of this.
I even seem to remember that website could have problems as well. They (the
hacker) did this: fill in a form you know that is ghoing to be send to a sql
server. Put a terminating quote into the entryfield, start type a new query
after that and watch it wreak havoc. You could send a 'drop tables' command
if you wanted to. Sure, the user connecting (set in the webpage/dsn/dsn-less
string) woukd have to have enough rights but in practice a lot of
webbuilders where not savy in security and granted the user-account they
used to connect admin rights allmost all the times. B.t.w, How's your
website doing Arnor?
Here's how I encountered it, perhaps that way it makes more sense.
Our software supports the creation and linking of documents. We used this
when we were still using tps files and in those files we added a reference
(filename) to find this document on a shared network drive. No problems. We
then shifted from tps to sql (mysql first, others are to follow in due time)
and amongst all the other 'stumbling blocks' when making the transition to
sql, we discovered that this wouldn't work anymore. Understand that we do
not want to go back to the shared network drive anymore. We had a lot of
troubles implementing remote office for our software. No matter what we
tried or did, it was always to slow and unreliable. Not only for the
documents but for the tps files as well, they are just not the right thing
for that situation. So we told ourselves that we only wanted to deal with a
sql server via tcp/ip and a portnumber. We can't and won't rely on an ftp
server for instance. We want total control and sql we can manage. So we
needed to transfer documents from the local harddrive of the user. No
problem I thought, just cut the file in reasonable packets and send it to
the server in a table. Retrieve the packets, slap 'm back together and
presto, the document is back on the local drive of the user. That's when we
started to mis things, like parts of the document. After studying the
transfer of the packets I noticed that some didn't arive complete, they were
broken. Then it dawned on me, there were characters in there that broke the
datastream to the sql server! Funily enough, the packets did arive, just
shorter than they were meant to be.
Then it was simply a matter of making the stream 'transferable' so that this
wouldn't happen. We zip the file up to make it as small as possible and then
we UUEncode this file. That file get broken into parts and stored in a Table
on the sql server. When needed we transfer all the packets to the client,
UUDecode it, unzip it and voila, a valid document. This technique ensures
that you can use a document in the office, store it, go home or wherever,
start our software and retrieve the document to work some more on it. You
could be on a cruiseship and still work with our software. Only thing needed
is tcp/ip connection and an ODBC driver. We tried this document thing on
remote location in the middle of the woods with realy sub-optimum
connections and it was still workable!
We use the LSZip compression library from Linder Software
(www.lindersoftware.com) to achieve this. It can zip files of course *and*
UUEncode/UUDecode them for you as well! Very nice indeed
One thing to remember though: when zipping you normaly reduce the size of a
file, but because of the nature of UUEncode you 'blow' it up a bit in size
when doing that. The bigger the file, the bigger the impact of UUEncode.
It's a trade of I'm willing to make though.
Now the real fun part comes when you stop thinking of those packets as
'documents' but rather as ordinary files you can store on the backend...
Can't begin to tell you the ideas I have for this technique. One obvious one
would be that we are going to store updates of the product in it's own
database. When you connect, first check if there a new file to retrieve and
install it if so then back to buisiness as usual. You would need a
pre-loader for your software of course (can't update what;s already running)
and there's a couple of other issues but anybody can work those out.
In the end I wrote a special DLL that handles all of the filehandling
neccesary to send and retrieve files.
We now have GetSQLFile(), PutSQLFile(), DeleteSQLFile(), RenameSQLFile() and
InfoSQLFile() functions that the developer can use just as if they were
using files localy, from folder to folder. But it ain't, we now better.
Must say that I had a *lot* of fun building this thing and it pays of in
usability and low threshold in using files in your application. I even
stored vidoes in the database, a 100 megs per piece! No sweat. MySQL flies,
vroom....
Sorry for the long post Arnor, but you probably know by know I can be a bit
lengthy.
--
Greetings,
Steven Spierenburg
Panta Relatiebeheer BV
stevenNOSPAM@NOSPAMjikade.com
Remove the obvious anti-spam word from the e-mail address for reply's
I want to rush for 1,000 or 1,500 yards, whichever comes first.
-- New Orleans Saint RB George Rogers when asked about the upcoming
season
"Arnor Baldvinsson" wrote in message
news:3e3ebcd7.5334093@news.softvelocity.com...
> Hi Steven,
>
> On Mon, 3 Feb 2003 17:54:24 +0100, "Steven Spierenburg"
> wrote:
>
> >string to a field (memo/large varchar) in the MySQL table you could be
> >transfering characters that signal the end of the query. I had this
happen
> >once and no errors were given, just truncated data in MySQL that I just
>
> Would this happen if that field was not part of the view in Clarion?
> I.e. would it happen no matter if the varchar field was in the browse
> view or not?
>
> Best regards,
>
> Arnór Baldvinsson
Printed May 2, 2024, 6:37 pm
This article has been viewed/printed 35113 times.
Google search
has resulted in 57 hits on this article since January 25, 2004.
|