At 04:37 AM 2/16/2012, you wrote: >I will not insist anymore on this subject. You're defending your approach >fiercely and "bending design to use sqlparameters" should be read as "doing >it right in the first place". Anyway, good luck duplicating an already >existing, fail proof, industry proven code. How would you know you found >them all? "&" + "&" can be written in more than one way. "&" + CHR(38) or >CHR(38) + "&" or CHR(38) + CHR(38) or whatever. A better approach would be >to concatenate first then test for troublesome characters, but in any case, >this approach drastically limits the "good" data. What if the username is >O'Hara? (yep, I tested first, it will error because there will be an extra >single quote). You won't allow that name? What if you have to send binary >data and you have a CTRL+Z ascii code in the binary data, which will break >your line, thus breaking the SQL command? I could rant on this for days.
It doesn't matter how I represent "&&". STRTRAN() and CHRTRAN() work fine with any combination of concatenated literal characters and CHR() output. So do "IF.. tests. "O'Hara works fine with IF LEFT(mylogin,1) == "'" because LEFT(mylogin,1) would be "O". I can't think of any valuable data that would be lost by refusing to let users enter strings beginning with single or double quotes into textboxes to be used as search terms or to be entered into a database. In what realistic scenario would there be a query interface in which a human enters binary data into a textbox? Actually, the only other one I can think of that I didn't mention would be the case where the query is expecting numeric data, and thus would contain no quotation marks around the variable. A person could insert a digit followed by OR .T. (or something) in that case, without using quotation marks. The easiest way to handle that is with a mask on the control that limits input to digits, but there are other ways. And at least one expert has pointed out that ? parameters don't prevent all forms of SQL injection and that the only truly safe way to do things is with something called "whitelist maps". I don't fully understand what that is but it looks like, oh, I don't know, um... parsing the query and substituting known safe values for questionable things the user enters! See: http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies pp. 58-64 Yes, you are ranting. Strict adherence to orthodoxy in the face of the inevitable exceptions will only lead to cardiac strain. :) >(And that nonsense about enterprise apps being hacked, well, you don't know >how they've been hacked. Maybe the hackers had physical access to the >database. Maybe they exploited an OS vulnerability. Maybe they exploited a >cross-scripting vulnerability. You don't know, you'd better not talk about >that.) Right. All of which vulnerabilities were written into the code by "experienced experts"; probably some of them were MVPs--though you're right, I don't "know" that. Seriously, your responses have definitely helped me understand the issue and improve my code, and I thank you for that. Ken Dibble www.stic-cil.org _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
