Many thanks.
A couple of very salient points that could allow me to use DirectDBMS
for my insert/updates with BLOBs. The hex/unhex is cool, but doubles
the packet size, so mysql's max_allowed_packet needs to be considered.
Haven't had a need for mass inserts/updates with BLOBs, but I do
something similar for a cross-reference table, building all the SQL
inserts dynamically so they can go in a single statement.
BTW, since you're on mysql5, can you issue multiple SQL statements in
a DirectDBMS, separated with semicolons? For example:
SELECT CURDATE();
SELECT * FROM users;
I'm pretty sure this works in mysql3.23/php4+, but it breaks in
witango5/mySQL4.
Anyway, back to the actual topic:
Has anyone done vulnerability testing to see whether any additional
escaping is needed to prevent SQL injection attacks?
bill
On Apr 4, 2006, at 1:26 PM, Robert Garcia wrote:
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
Bill
William M. Conlon, P.E., Ph.D.
To the Point
345 California Avenue Suite 2
Palo Alto, CA 94306
vox: 650.327.2175 (direct)
fax: 650.329.8335
mobile: 650.906.9929
e-mail: mailto:[EMAIL PROTECTED]
web: http://www.tothept.com
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
