We try to use insert/update/delete actions whenever possible. But
have at many times had to use directdbms.
One very good use of it, is to prevent looping in witango. For
instance, I have software connecting to a webservice, and I send xml
for witango to insert many image categories. Usually a few hundred,
but can be as many as a couple thousand at one shot. Even a few
hundred, doing a for loop, and insert action, can bring witango to
its knees. We have tried everything like turning foreign key checks
and autocommit off, surrounding in a transaction, all to conclude, it
is not the db, but witango loop slowness. I built a witango helper
that listened on a port that witango passed the xml to using <@url,
and the helper did the loop insert, and it worked great. But I have
been trying to avoid this kind of solution as much as possible.
After playing with mysqldump a bunch of times, I decided to try to
build a "dump text" and send to witango in the xml. So the dump text
is a massive insert statement. I pass this statement to witango, and
witango does 6 direct dbms actions. set autcommit = 0, set
foreign_key_checks = 0, then does the mass insert, then reset the
mysql vars from first 2 statements and manual commit. This solution
is even faster than the helper. 1500 categories will lock witango,
and kill it with the loop. This takes about 2 seconds as a mass insert.
There have been other instances, where we have had to even insert
blobs, and for whatever reason, could not do as insert statement. And
as Bill has made use aware, there is a 32k limit on <@bind>. However,
there is no limit I have found to directdbms, and I ALWAYS test with
the hardest possible scenario.
With both the solutions above, and because bind is NOT available to
me with the limit, we have had to practice escaping. We have a
mysqlescape method that has worked for us with everything, including
blogs of jpeg images, which usually pretty nasty to put in an insert
statement.
Function mysqlescape(s as string) As string
dim res as string
dim i as integer
if s.len = 0 then Return "NULL"
res = s.ReplaceAll("\","\\") //THIS MUST BE FIRST, or "\" will be
double escaped.
res = res.ReplaceAll(chr(39),"\"+chr(39))
res = res.ReplaceAll(chr(34),"\"+chr(34))
res = res.ReplaceAll(chr(0),"\0")
res = res.ReplaceAll(chr(10),"\n")
res = res.ReplaceAll(chr(13),"\r")
Return chr(39)+res+chr(39)
End Function
This is a realbasic function, but I think you get the idea.
From my reading, you don't have to escape newline, or return, but I
do, because it keeps each statement on one line.
It is important to remember, that when building an escape method, you
must take the db vendor into account, this works for mysql, and you
should do this method on ANY string to be safe.
There is another problem with witango on this, witango will choke on
the NULL (\0) char in a string if you try to do regex, or replace, or
any string function I have tried. So you cannot perform the
mysqlescape method within witango on blobs or any binary data. With
binary data in witango, you can pass it around, but not really do
anything with it.
The best method to do this type of escaping in witango would be in a
BEAN, which should be pretty darn easy, I just haven't had the need yet.
There is one other method, if you need to escape in witango, that is
pretty cool, but has some overhead. If you have a jpeg blob, or some
bin data, in a var called local$bindata, for example. And you want to
insert in a direct dbms action, you can do this:
<@assign local$bindata <@CIPHER ACTION="encode" TYPE="Hex" STR="<@var
local$bindata>">>
insert into imagetable (rowid,myblob) values (1,UNHEX('<@var local
$bindata>'))
This will work, if you have to escape something in witango, but can't
search and replace null chars or other bin chars.
You can even use this in an insert or update action, not sure why you
would, but just turn quote value to OFF, and insert UNHEX('<@var local
$bindata>') write in the value item. This would be helpful if you had
to pass data into witango, that you could not escape and you passed
in hex. Otherwise the witango built in data binding should work fine,
unless of course you hit the !CST bug. :-)
I am not sure if I am completely answering your question, but I have
been up to my eyeballs in converting witango/primebase to php/mysql,
and primebase to mysql is the first step.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park DrUsua
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Apr 4, 2006, at 7:17 AM, Jason Pamental wrote:
Bill,
I was wondering about this recently also and found some interesting
tidbits. One is that at least in SQL Server, when you use an insert/
update action, the SQL is actually executed by Witango calling one
of the built-in stored procedures to execute the sql passed to it
by Witango. So SQL Server's built-in protection against SQL
injection through stored procedures is in place. However... when
you use a Direct DBMS action, it looks like it's directly executed
SQL, not through the use of a stored procedure, so therefore you
would have some exposure here if you were using a direct DBMS
action to insert or update something with contents from a user
form. I'm not certain that this would be a real risk, but looking
at the trace log in SQL Server it seems to be the case.
I don't know how different it is in other DB's, but at least
according to MS if you use stored procedures to process queries,
you are protected from SQL injection attacks.
Anyone else done any research on this? (I'm betting that Mr.
Shubert and Mr. Garcia have some experience here, among others...)
Jason
William M Conlon wrote:
I've been trying looking for vulnerabilities by attack my Witango
code a la
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
It looks like the Witango's SQLENCODING is doing the trick.
I guess it's like magic_quotes() in php. There seems to be a lot
of controversy about magic-quotes, mostly philosophical, about
preferring to escape instead. I don't want to start a debate
about escaping versus doubling single quotes.
But I would like to hear if anybody has found the need for any
other SQL filtering.
Bill
_____________________________________________________________________
___
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Visit us at http://www.northsails.com
[This E-mail scanned for viruses by Declude Virus]
--
Jason Pamental
Director of Web Services
North Sails
Office: 401.643.1415
Fax: 401.643.1420
Mobile: 401.743.4406
Email: [EMAIL PROTECTED]
______________________________________________________________________
__
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
