Looking at this:
http://www.unixwiz.net/techtips/sql-injection.html
both the escape and hex method, should prevent.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
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 1:52 PM, William M Conlon wrote:
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
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
