Wednesday, July 13, 2011

Rules of (a) thumb for preventing SQL injection(s)

"Rule of thumb - 1) a method of procedure based on experience and common sense; 2) a general principle regarded as roughly correct but not intended to be scientifically accurate" - Merriam-Webster dictionary (link)

SQL injection is officially Top 1 Web Application Security Risk in current OWASP Top 10 list. Hence, it should be approached with highest care. Here you can find notes/rules taken from experience acquired in dealing with noticeable number of such vulnerabilities.


1) Don't rely on "escaping" (e.g. addslashes())
  • Also known as "HTML sanitization"
  • It won't help you with integer and/or date values (to be honest, you could use hard typecasting here like intval() but in the long run you'll just mess and/or forget things up)
  • Escaping of string queries can be circumvented (example: http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string)
  • You'll end up with unnecessary complex code which potentially will result in new "bugs". Also, as I am a programmer myself, I can tell you from my own experience that programmers tend to "loose the complexity of code" and this just clears the way for new potential bugs/problems/injections
2) Don't (solely) rely on WAF/IDS/IPS for plugging your potential holes
  • I don't have anything against usage of those as a last line of defense, and I know from my experience that there are already TOP people working on those (kudos goes to Mario Heiderich (@0x6D6172696F) as a true expert), but plain installation of those as a MITM defender for plugging some of your lazy programming "tryouts" is like putting a big cannon at the center of some capitol with unlocked entrances to all houses around. Generally, it will do the job, but won't protect you in the long run. When people are eager enough they'll eventually find holes in those (obvious example: ModSecurity SQL Injection Challenge)
  • Notable mentions can be found here: "Top 10 Open Source Web Application Firewalls (WAF) for WebApp Security"
  • Lots of bypass examples (kudos goes to Roberto Salgado (@LightOS)) can be found here and also a great reference can be found here
3) Don't rely on "blacklisting" methods
  • "Blacklists" (e.g. list with common SQL keywords SELECT, UNION, INSERT,...) are easy to circumvent (e.g. with usage of inline comments like SEL/**/ECT) and will trigger false positives in the long run (to be honest, you can do it in the low traffic site, and putting keywords like: SELECT, FROM and WHERE will keep 95% of attackers away, but those 5% will eventually find a way)
  • Experts at WAF/IDS/IPS do this already far better than you'll ever do
4) Don't rely on "new" and "unconfirmed" ("snake oil") methods
  • Please, don't you ever trust people that don't have experience in this field. I've been struck by a "new method" of prevention called "inverted queries" (How "·$% developers defeat the web vulnerability scanners). In short, author claims that you should use "SELECT * FROM users WHERE '.$_GET['id'].'=id" instead of: "SELECT * FROM users WHERE id='.$_GET['id']"'. This can be easily circumvent with a PLAIN SQL comment (e.g. '-- ') at the end of injection payload
  • Obvious example of marketing tricks and selling of "snake oil" is "HP's Webinspect Real-Time" (Pushing Technology - "Blind SQL Injection" is Dead) - "Forget it, those days are over."..."Essentially, as the ... title says ...no more Blind SQL Injection ... because we're no longer blind.". Any comment is really unnecessary, than the following. I'll gladly have a public discussion about the techniques used in that "silver bullet tool" if they are willing to. That kind of false marketing is called "Inducement - False Representation". Why do I say that? Because they are using fraudulent misrepresentation of solving "blind injection" problem in terms that "they are no longer blind". Anyone telling you that they have a "silver bullet tool" is lying at least, especially if they are after your money
5) Remember, no DBMS is "safe"
  • You have to know that "SQL injection" is not inherently the DBMS fault, but the "lazy/unknowing programmers" fault. DBMS just executes commands "crafted" by the programmer (and attacker if there is a flaw at the parameter level). Manual concatenation of parameters to the SQL query "skeletons" (e.g. "SELECT * FROM users WHERE id='.$_GET['id']"') will always result in "SQL injection". It doesn't matter if it's done at the higher language level and/or inside SQL stored procedure, "dumb" concatenation of parameters to SQL queries is a "deadly sin" (recommended read: Web Application Sins).
  • If the backend DBMS supports "subquerying" nothing stops attacker(s) from "snooping" around if the sql injection hole is present (e.g. MySQL < 4.1 doesn't support "subquerying" but you mustn't use such outdated versions as they inherently have other deadly/exploitable problems - e.g. MySQL 3.23.x/4.0.x remote exploit).
  • Lack of "information_schema"-like table (e.g. like in Microsoft Access, MySQL < 5,...) won't prevent attacker from brute forcing the table/column names (e.g. --common-tables/--common-columns switch(es) in sqlmap)
6) Turn off (or at least restrict DBMS) error messages (!)
  • First thing that all attacker(s) do is a deliberate changing of parameter value(s) with some invalid SQL form (e.g. ')('"(). If they are able to provoke visible change (worst case scenario is a detailed DBMS error message) they'll "smell blood" and continue with the attack for sure
  • Error-based injections (in short, putting subqueries into the erroneous sql form and retrieval of the results from the error reports - lots of DBMSes precalculate subqueries and report back the results in the form of error message) are quite popular and among fastest ways how to retrieve data. Also, error reports are a indispensable help for the attacker as he/she'll immediately know the type of backend DBMS - this shortens the time of attack by a factor of two
  • Turn (at least detailed) error reporting off in a production environment (e.g. PHP). It won't help you to prevent SQL injection attacks but in a long run you'll at least know that you won't provide help to attacker(s)
  • All popular sites prevent information leakage in form of suppressing this kind of output
7) Do use "parametrized" SQL queries (!!)
  • SQL drivers and libraries will automatically "sanitize" input to parameterized SQL statements. In simple terms, parameters inside those kind of statements are really treated as "values" and not as a potential part of the "queries". This means that the parameter values can't be used for building SQL queries (excellent illustration(s) can be found at (slides 42-46): Sql Injection Myths and Fallacies)
  • Easy and currently the best way to avoid SQL injection holes/attacks and deal with this whole problem (i am highly biased because this really works)
  • Programming examples for bundle of programming languages can be found at:
8) Use the least (possible) privileged user account to access DBMS resources (!)
  • You should always consider worse case scenario(s). In this case you should expect someone breaking into your DBMS through SQLi
  • Worse thing you can do is running the DBMS connector (query interface between your web application and backend DBMS) with highest database privileges (e.g. "DBA"). This is very common mistake which provides the attacker vast list of possibilities for data retrieval and/or complete takeover
9) Don't (solely) rely on (SQLi) tool reports
  • Pentesting/security/SQLi tools have quality as of people working on them (honestly, good ones dealing with SQLi can be counted on a hand with missing fingers)
  • Majority of "popular" ones are prone to false positives (!) and are relying solely on error-message recognition (malpractice used by lots of them - parsed error message(s) do(es)n't prove anything)
  • Not a single tool can guarantee you with 100% accuracy that the target is NOT prone to SQL injection attacks
  • Manual exploitation is used where tools fail. If there is a hole to be exploited, be sure that eager attacker will eventually find it and use it against your will
  • One final (gratis) tip. All commercial tools use only the error-message parsing along with the simpleton blind injection payloads (e.g. AND 1=1) to check for the SQLi existence (this can be coded in less than 100 lines of code! - (if needed) i'll gladly do it out of fun on request by people offended by these lines). Don't you ever trust them. Searching for SQLi is a far greater problem than "parameter poisoning" with values like ')((''. If you need proof, just scan your web page with int()-typecasted parameter values and turned on FormatException messages - they'll all fail with false positives. They won't provide the through testing (out of sheer ignorance) but the attacker will. These people don't use (commercial) tools, I can guarantee you that, and you'll see there lots of hacked high PR companies that still use these kind of commercial "half-products"

p.s. and answer to you question is no, SSL won't help you against SQLi
p.p.s. couple of days after this post i've done that tool under 100 lines of code mentioned in lines above :)
p.p.p.s. if you really need to stick with the old "concatenated" code best advice I could give is to go through the list of Most Popular Wordpress Plugins (link), search for "mysql_" inside the code and learn from their practice. I won't claim that they are all free of SQLi but I can guarantee you that those at the top are doing a fine job protecting against this kind of threat(s)

20 comments:

Anonymous said...

Wonderful text! A must to read!

Anonymous said...

good resource to start with...

Anonymous said...

dE7G7o http://stivalisale.webs.com fR6L3b [url=http://stivalisale.webs.com]ugg stivali[/url] yP4F3i stivali ugg tL4J2r
yQ9T0m http://www.vipbottespascher.com rT3X3h [url=http://www.vipbottespascher.com]ugg pas cher[/url] eU3G3x chaussures ugg lP4H2e
qX3B4u http://www.bottespaschershop.info dG8D6p [url=http://www.bottespaschershop.info]bottes ugg pas cher[/url] tG8E4g bottes ugg pas cher tD8D0c
zS7F1g http://goedkopeboots.jouwweb.nl yT1E8d [url=http://goedkopeboots.jouwweb.nl]goedkope uggs[/url] bS3U8o uggs winkels zV9Y1j
tN0T1i http://www.isabelmarantde.info aK6I7q [url=http://www.isabelmarantde.info]isabel marant[/url] rD3O1o isabel marant sneakers wC3B4i
aW3S4w http://frbottesspascher3.webnode.fr lZ1P1g [url=http://frbottesspascher3.webnode.fr]bottes ugg[/url] zN9O9a bottes ugg wR1X6e

Anonymous said...

zTcy ghd hair straightener
bOgm ugg boots sale
iLnp michael kors handbags
2eTdx cheap ugg boots
2bMbj chi flat iron
0fLgq ghd straightener
0gJkt nfl jerseys
6oDpj ghd nz
8fNls north face jackets
1hPcf ugg australia
9oIbf ghd pink
6sFyv michael kors purses
9uRsa nfl shop
2dCxy ghd planchas
6eNsx ugg store

Anonymous said...

xHuk coach outlet
kLqj cheap ugg boots
tAvq michael kors purse
9cRtq ugg uk
2yCre chi
3sAbh Michael Kors
9aKhl nike nfl jerseys
0eMtz coach outlet online
3cCmk cheap north face
9sSbt ugg baratas
3uFry red ghd
9iLqu michael kors outlet
4wGcn nfl jerseys
5fZnf ghd
1xJly discount ugg boots

Anonymous said...

7gXfq ghd australia
gPwf ugg boots uk
xYyl michael kors purses
3jZar GHD Hair Straightener
8nDet burberry sale
0qLic chaussures ugg
1lFdr ghd
8vRcv louis vuitton bags
6sEse michael kors handbags
9cUrm coach outlet
3eYvb ugg uk
6dXys cheap nfl jerseys
5mSjx michael kors outlet
4sZfj lisseur ghd pas cher
8rHbi discount ugg boots

Anonymous said...

http://bloggervillage.com/index.php?do=/blog/5026/costing-%C3%A2%C2%A335-on-a-12-month-plan-and-includes-%C3%A2%C2%A35-of-free-downloads/
http://cinecloudfilms.com/index.php?do=/blog/92474/that-is-on-your-computer-by-protection-is-better-than-a-cure/
http://www.webshare.cc/blog/b/blog_view.php?mid=547194&id=147&show_bbslink=
http://sns.cam111.com/blogs/entry/There-are-umpteen-options-available-to-the-residents-of-UK-when-it-comes-to-mobile-connectivity
http://www.chumclubs.com/blog/30648/costing-%C3%A2%C2%A335-on-a-12-month-plan-and-includes-%C3%A2%C2%A35-of-free-downloads/
http://tiredoftalk.org/jcow/index.php?p=blogs/viewstory/741
http://www.benches2swings.com/vocab/catpath/gamesinteractive-design.html
http://cinecloudfilms.com/index.php?do=/blog/92473/there-m-a-r-c-b-y-m-a-r-c-j-a-c-o-b-s-%E3%83%90-%E3%83%83-%E3%82%B0-is-presently-only-one-option-fo/
http://d.hatena.ne.jp/rrrtt/20130128/1359357923
http://archive.remdublin.com/blog/xmyshang/2013/01/27/general-way-writing-telephone-number-sign-attached-country-code
http://heraldbulletin.neighborsink.com/node/247765
http://xmyishang.exblog.jp/17225971
http://www.toma.jp/blog/333333/?entry_id=870629
http://d.hatena.ne.jp/rrrtt/20130128/1359357889
http://xmyishang.exblog.jp/17225972

Anonymous said...

It¡¯s better to have fought and lost, than never to have fought at all.
http://www.michaelkorsoutletez.com/
http://www.longchampsaleukxs.com/
http://www.buybeatsbydrdrexa.com/
http://www.cheapnikesshoescz.com/
http://www.uggsaustralianorges.com/
http://www.burberryoutletsalexs.com/
http://www.nflnikejerseysshopsx.com/
http://www.cheapnikeshoesfreerun.com/
http://buy.hairstraighteneraustraliae.com/
http://www.cheapuggbootsaz.com/
http://www.buybeatsbydrdrexa.com/
http://www.cheapfashionshoesas.com/
http://www.cheapsfashionbootax.com/

Anonymous said...

http://www.chumclubs.com/blog/30651/having-too-many-spyware-remover-039-s-or-running-on-your-system-actually-co/
http://heraldbulletin.neighborsink.com/node/248445
http://www.heavenlysins.com/index.php?do=/blog/46760/when-you-use-a-spyware-remover/
http://d.hatena.ne.jp/jiumengshici/20130128/1359352829
http://archive.remdublin.com/blog/xmyshang/2013/01/27/general-way-writing-telephone-number-sign-attached-country-code
http://aaa333333.cocolog-nifty.com/blog/2013/01/get-spybot-prot.html
http://heraldbulletin.neighborsink.com/node/247693
http://jiumengshici.hatenablog.com/entry/2013/01/29/111951
http://www.bloglog.com/blog/xmyshang6/126479/the-pay-as-you-go-is-the-most-convenient-and-straightforward-modes-which-comes-without-any-hidden-or-the-monthly-fees
http://www.heavenlysins.com/index.php?do=/blog/46932/the-on-tory-burch-riding-boots-foot-navigation-is-a-great-additional-featur/
http://www.bloglog.com/blog/xmyishang/126661/like-the-mobile-phone-industry-that-has-witnessed-a-boom-recently
http://www.benches2swings.com/vocab/catpath/there-was-urgent-need-%E3%83%86-%E3%82%A3-%E3%83%B3-%E3%83%90-%E3%83%BC-%E3%83%A9-%E3%83%B3-%E3%83%89-%E3%82%B9-%E3%83%8B-%E3%83%BC-%E3%82%AB-%E3%83%BC-replacement.html
http://hollar.se/index.php?do=/blog/2358/spyware-will-not-make-itself-noticeable-until-it-is-to-late-and-then-you-ha/
http://www.bloglog.com/blog/xmyshang6/126030/the-technology-in-question-m-a-r-c-b-y-m-a-r-c-j-a-c-o-b-s-is-wi-fi
http://www.mymarburg.com/blog/76809/this-can-%E3%83%9F-%E3%83%A5-%E3%82%A6-%E3%83%9F-%E3%83%A5-%E3%82%A6-%E8%B2%A1-%E5%B8%83-be-even-more-true-if-you-use-refilled-ink-cartridg/

Anonymous said...

There is no disputing about tastes.
http://www.casquemonsterbeatser.com/
http://www.cheapfashionshoesas.com/
http://www.coachfactoryoutletsez.com/
http://www.michaelkorsoutletez.com/
http://www.ghdnewzealandshopa.com/
http://www.nflnikejerseysshopxs.com/
http://www.buybeatsbydrdrexa.com/
http://www.bottesuggpascheri.com/
http://www.burberryoutletusaxs.com/

Anonymous said...

UwsIcu [url=http://onnrainnmcm.com/#82572]MCM[/url] ArpWct http://onnrainnmcm.com/ LjyZws [url=http://mcmsenmon.com/#02891]MCM 財布[/url] RstIee http://mcmsenmon.com/ WkuOwj [url=http://ninnkimcm.com/#31615]MCM 財布[/url] CewIoy http://ninnkimcm.com/ HmyRpx [url=http://kaidokumcm.com/#73754]MCM バッグ[/url] PunFeq http://kaidokumcm.com/ RhqBzq [url=http://manzokumcm.com/#22585]MCM バッグ[/url] DpwVmp http://manzokumcm.com/ IoiDxs http://chloenihon.com/ OvkPaf [url=http://chloenihon.com/]クロエ キーケース[/url] DthA http://toumikousin.com/ SvzPO [url=http://toumikousin.com/]TUMI 26141[/url] ChmZuO http://toumikakaku.com/ HgjFsYM [url=http://toumikakaku.com/]TUMI トート[/url] BauHqm [url=http://mcmhannbai.com/]MCM 長財布[/url] NaaZge [url=http://mcmhannbai.com/]MCM 通販[/url]

Anonymous said...

Learning is like rowing upstream; not to advance is to dropback.
http://www.buybeatsbydrdrexs.com/ 5j4l4l1p7t3h5j4p
http://www.cheapnikesshoescs.com/ 7i8z6a3e9l1d0j5t
http://www.cheapfashionshoesas.com/ 5x6t0t9d9e2m0f9x
http://www.michaelkorsoutletei.com/ 2i3h0y8b4h5f4r4t
http://buy.hairstraighteneraustraliae.com/ 9z6p9e8r8r0k5t4h
http://www.nflnikejerseysshopse.com/ 0h5k1b6f1t1p2o4j
http://www.uggsaustralianorges.com/ 9o8r0j2v2h5i1e8x
http://www.cheapnikeshoesfreeruns.com/ 8c8b7x7e0n7n1v9p
http://www.cheapbootsforsale2013s.com/ 2b2k7k9t9z6y0m8c
http://www.burberryoutletsalexs.com/ 0h4z3k1h5k7p1d7p
http://www.longchampsaleukxz.com/ 4k5e7l9h1t3f2k0g

Anonymous said...

coach outlet jvormpil coach usa ccmwnetr coach factory outlet uukmpgtt coach factory cncckxtu

Anonymous said...

cheap ugg boots sale kvnaeszo cheap ugg boots uk itihhahg cheap ugg boots fpbgwtxg cheap uggs xbeomztz ugg boots sale uk yvexddcn ugg boots sale yxqicgfm ugg boots uk agizcgpp ugg boots rapuxbem ugg sale whhhabef

Anonymous said...

ghd nz yjukfxoa ghd nz sale maxzcqbb ghd exgcoeqr

Anonymous said...

cheap ugg boots sale eldgzlnl cheap ugg boots uk cosvdvxc cheap ugg boots omohzoiz cheap uggs ajndnzwp ugg boots sale uk xkcawuvr ugg boots sale bppgvzwc ugg boots uk zevuhspq ugg boots yoyzkggz ugg sale prwspdbs

Anonymous said...

beats by dre iopbsjik casque beats by dre pas cher eafxfnxv casque beats by dre leczrefg casque docteur dre zwyqrdph casque monster beats pas cher zsuvdhaf casque monster beats dozdgmtb ecouteur beats wzfaabgu monster beats pas cher mnrlgbav monster beats vqhvjuqt

Anonymous said...

nike norge amiyybop nike shox dame zewucmgw nike shox norge gqhszrdo nike shox sko netkipvy nike shox dsnstehu nike sko norge roirdrtu nike sko p? nett wtsxxwen nike sko mqxqdjyz nike eowhhcyw

Anonymous said...

cheap nike air max 90 vehierco cheap nike air max imhtpzpn nike air max 1 dxctlpom nike air max 90 xfgrhedf nike air max 95 wmqbsxcf nike air max hwmgyphg nike free run mhnmyrek nike store uk vpnknpqs nike uk cichculm

Anonymous said...

Thank you, I have just been searching for information about this topic for a long time and yours
is the best I have discovered so far. But, what concerning the bottom line?

Are you positive in regards to the source?

Here is my blog post - http://www.naughtyadultprofiles.com/Jimmy33N