SQL Injection

Learn about how SQL injection works and its impact.

We'll cover the following

Storytime

Let’s start with a story. Mike is the system admin for a small private school. His main responsibility is maintaining the network and computers. Recently, he started automating various tasks around the school by building a web application for internal use. He has no formal training and minimal programming experience. Knowing the basics of PHP, he built a pretty stable customer relationship manager for the school and even received kudos from the superintendent for streamlining operations and saving the school money.

Everything was going well for Mike until a particular new student started. The student’s name is Little Bobby Tables. One day, Jon from the admin office called Mike to ask why the system was down. After inspection, Mike found that the table containing all the students’ information was missing entirely. You see, Little Bobby’s full name is actually “Robert’); DROP TABLE students;–”. There aren’t any backups of the database; it has been on Mike’s “to do” list for a while, but he hadn’t gotten around to it yet. Mike is in big trouble.

Beware of falling tables!
Beware of falling tables!

SQL injection

Real world

While it’s unlikely a child’s name will contain harmful SQL code, this kind of SQL injection vulnerability happens in the real world all the time:

  • In 2012, LinkedIn leaked over 6 million users’ data due to an undisclosed SQL injection vulnerability.
  • In 2012, Yahoo! exposed 450,000 user passwords.
  • In 2012, 400,000 passwords were compromised from Nvidia.
  • In 2012, 150,000 passwords were compromised from Adobe.
  • In 2013, Harmony had roughly 1.5 million user passwords exposed.

For most of these, precise details are undisclosed. We can’t be sure these were due to SQL injection attacks, but it is likely.

How SQL injection works

If you use user input without modification, a malicious user can pass unexpected data and fundamentally change your SQL queries.

If your code looks something like this:

UPDATE users
    SET first_name="' + req.body.first_name +  '" WHERE id=1001;

You would expect the generated SQL to be:

UPDATE users 
    SET first_name="Liz" WHERE id=1001;

But if your malicious user types their first name as:

Liz", last_name="Lemon"; --

The generated SQL then becomes:

UPDATE users
    SET first_name="Liz", last_name="Lemon"; --" WHERE id=1001;

Now all of your users are named Liz Lemon, and that’s just not cool.

SQL injection in action

If preventive measures are not taken, SQL injection attacks can cause many problems. Let’s inject some SQL code. Hitting the RUN button will open a web application. The edit function is vulnerable. Try to exploit it!

Open the application in a new tab by clicking on the URL below. If you perform any injection attack, refresh the page to see the changes.

 ãF )   95@@ °n‰PNG


IHDR(-SäPLTE""""""""""""""""""2PX=r€)7;*:>H¤-BGEˆš8do5Xb6[eK™®Kš¯1MU9gs3S\I“§:gt'03@{‹V¹ÔT´ÏA}V»Ö@y‰6\fH’¦-CII”¨Eˆ›+;@7_i7_jFŠJ–«K›°H£-BHaÚû,@FCƒ”L³&.0W½ÙN£ºI“¨$)+B‘J•ªR¯È?v†>s>u„S±Ê=qP©ÁP¨ÀP§¿,?D4U^%+-M ¶K˜®%+,2OX+<ALœ²#&&D†˜%,.I•©vôTötRNSIæçJäeÀe¦©IDATxMŽµZEA„ÿÙ³	îî%R¡ïßáTThÇG…»,Á®Å=²Òîmífímnf’A–$â‡>!¦gºôHg½Eߏܵ}	Ý»ý‡º¼kdú§¯Jo—™Î3æL"J¹ ›ÌÕüQ‡$âçļffµ,é€5i9̟¯H¨/mB†‡wÍÜw;D
Ø+&‚W«ª¹¨Dôo@Ê´RI©ÐB¡om.Û³ÀIEND®B`‚‰PNG


IHDRשÍÊePLTE""""""""""""""""""""""""2RZN¢¹J–«3R[J—¬)59YÁÞ0KS4W`Q«ÄLœ²%+-0JR)6::gtC‚“"##?v†U·Ñ?w†<n{&-/YÂß=q:iuBA}A{ŒB‘/IPP§¿=q€K™®_ÔóL³$();lzR¯ÉaÚûI“¨ZÆã3U^1MU3T]ZÅâI“§X¿ÜF‹ž-BGP¨À6[e,@E5ZdO§¿-BHX¿Û+=AW¾Ú,@FW¼ØQªÃ?v…W¼×+<A@yˆ"#$\Ìê4Wa\ÌëS²Ì$(*.EL^ÑñVºÕ6]h#$%GŽ¡#&';jwV¹Ô-CIL›±ZÄá^Ðï>u„S°Ê/HNM ·_Õõ\ËéM ¶8doD…—D†˜>tƒ+=B[Èæ,>C>t‚<o}@y‰0LS.EKT´Î$'(%,.A~ŽW½ÙC’%+,\ÊèC!ätRNS‘í‰œG¾ÖOIDATxl‰ÃB¶Q…Ÿu´ß_ȳ<˦Ýveê²óa6AξŒûv¢{@ÎE'ÞdIÕ!çží ðC—ÔT‹þg1ÂE(ÏñSQsâi
ď…Zÿ·V¹Ð)ég!‰ªhÎùtéº-i}˜µµ<Õ?¶lBZaÄ´4{DÓ⌻_e8¥yǁ­À3ž)Ÿ¥?°f;8.ã¤tÌ=å;	:ã52fKZìlù¨ØšÍ9.ž#ƒÒAÁqÌúÛ®£Vÿ`=$¬Â?_¶¾®ÔqMç.ïJ$
?^q÷ñíۏï.},‚ìsæÝ_TttÔ¾1#‰/(ì—-[è`è`ÌÚïÅðZd5’Ž™›?ÎebZ¿Þˆi.Ûæ™ìq΄+1°}Œ5ùïçd¨G•ÏøIEND®B`‚‰PNG


IHDR  D¤ŠÆAPLTE"""""""""""""""""""""""""""2RZVºÖ_ÔôU·Ñ=r€$()'25]ÎíCƒ•0LS<o}XÀÜX¿Û0JQ=p~D„–<n{VºÕE‡™8do_ÔóEˆšF‹žH‘¥9dp_ÕõH¤I“¨FŒŸ6[e`Ö÷`×øL³/GM_ÓòU¸Ó'02P©Á/IPPªÂX¿Ü&/1;ly3R[`ØøGŽ¡T³Í\ÌêaÚû1OW"##Q«ÄaÙúR®Ç=q€`Öö.EL+=ATµÐ-CIK˜®#&'C‚“^ÐïI”¨&.04U^^Ñð@yˆZÇä$(*[Éç^Ññ,?DR¯É"#$1NV1MTD…—>u„;kxG R¯È/HN&-/@y‰>s>t‚@zŠ]ÍìP¨À$'(D†—]Ïî<n|0JRU·Ò×\¼	tRNS%­ñ'ïó(ò~ÑÝèžIDATx“šC1F_Ý¿MmÛ¶4¶m{ÿ˜¤n†çáÓ	®A$–à$b‘ Heø™TãWÄÂh•šh´:PtZ
Q«0@.`€Þ`4™-V`³Zì&‡A#ÁébkÝÄãñúØ>.''ø`C$FØÏ	‘(±x"6Xác”TÚéL§@Iù;dd-¹|¾P,Ȕ9¡R­ÕÍf3¢¿F½VmMíX§ÚíÍç@Y˜7ÎõºÕN¬=—ŽåÈʪu
}Ö¬«+‘e‡aiq ¤Ö76­íÝ=h
ûZìíîl›ë‡}á¨ʱ¥[F«I9A¹k9¥ÖëäŒ3¢Ã9ΡóžqB~Øáb¸ÃåU_¸^Ü[·ôw†ý{z‡v‡z‡Ù(Š£(Š£(þ›†Šfòq”ÉG–Éïkñ”ÏçŠY¾ÿªfäòÇ~à:*4ÓQ\O>Ÿ‘ ‡¼<דúW£éÍZ|ދÅ7“ñ•ïjTÔäãn”½»¢®`$Hð+ò¿GOñûð*èx‹•ø¥X*|”^ÿdIEND®B`‚‰PNG


IHDR@@·ì:PLTE"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""%+-@y‰W¼×`×ø^ÑñS²ÌC‚“,>C*8<XÁÝaÚûaÙùMŸµ+<AaÙúXÀÜ#%%TµÐLž´=q>u„K›°`ÖöA}L›±8do=r€%+,@yˆ^ÐïS²Ë)59=q€P©ÁU·Ò"#$PªÂ\Êè0JQQªÃ"##U·Ñ#&&_Ôô>t‚>s`Øø_Ôó5Yc1OW5Zd1NV+=B1MU+;@/GM\Ìê*;?3S\)8<2RZ_Õõ+=A]Ïî,@F,@E&-/0KS7alO¦¾9dp8amB~EˆšP¨ÀN¢¹'023T]]Îí?x‡3U^Cƒ•6\gU¸Ó&.0D„–7_iR­ÆH‘¥I”¨M ¶$(*?v…ZÆãX¿Ü-AG#$%[Éç8co[ÈæW½ÙC’'25?v†8bn%*+Lœ²N£º2PX)7;=p~(58^ÒòP§¿4WaQ«ÄT´Ï0JRQ¬ÅT´ÎI“¨6]hR¯ÉT³Í0LSF‹9eqEˆ›E‰œ9gsFŠCƒ”#&'\Ëé`Ö÷&/16\fB‘A{ŒR®Ç]Íì(47%,.*:>*9=9fr:gt7^iU¶Ð?w†ZÇäX¿Û^ÑðQ­ÅH£)6:V¹Ô'034U^E‡™.EL.FMK™®@zŠS×tRNS*Ž×øÖ”ý˜	»½•+üùóԐ,ØúôÀ=VIDATx¤ËµC!Ð‡C|ÿãÚ^yR]ÕMÛáO]ßÔÕÝ0NÈ2ÍËí¿"ªª¢(0Vã(ÀY%PDT-~(m¬ó!âKÞY£~´•üIÒf{³ÛÞáa¼§§ô3—ÕOp&”Ф‰¡xŽ÷#Ÿj­ôÚ¶mméòc)]m¤’‡É)Ƨgfçhk²ñÎÒ ægg¦Ç™šìÐ+X€ÅêuiyVת·k«°²\[ü:,Ø6ØÜjIJ
;»"»;°×XùþÛfÁáÇÍûý“SÎÎÏÏ8=Þo¾;æèÐ(ƒ‹öÓ¥BkÔeûÍ\7p+mîîáþNÚ<ÀQðOÒæùô³´y‚g»ttÐëo•ý½£ŸìVð»Òäsýü¬™ø”&_ðaüïV~à·Ö?­*8àQ ;8¥Á,¸¤‚f¥“1Üx¤†×§ñŸ*œøÑA¯Ôð°a#±³¶¦#ŠnP‘i+¼¶CÈ,ˆÆèäÍ_áNbÑá‚øç•HŽB*ÚÒ¦ L(^<ñÁ‚L6pJ¾P”É¥Ž©¢%"“R,ä9Èe3eRËa1(
¢ßqÇ8َ´ŠmK˱mƶmÛü·yi!èΪYÏuë ÀÏ_Àï?i÷ˆý+òŠÄA|ù{‘˜´?¿_En).JËD¤<€
©¬¢Z\Ts©R*(	¯©JŠ…uX/
4J9š¡5·DEµ4kÇ4‡&i¥V4Ú¡®Ð¯†vsf:àg,¢èBC»î$¶ºÍùá–@ôŠI_?<‘!^ŠÈàÓ½ÇöäõB‘%Làw±FD1ŠÁ¨(F€±øH˜%0Ʊ¿Å؄(¢0ˆÅÄ'Åæ—N.0u„@íY‡PWìIüaNâKš™Ä?ðÓµŒ=Žeœv/c—±ŒÓ0c0÷2Êë:ˆ06R-uÒÄ­\Q̶ää´¼µ6R#
ÆFš³6Òñ·rՁ­ìu˜æmâðž‡Iñi~ –Åü ÃsPþ"±óŸ¼
eiyå£ËPšàãÊò’§¡œÝÒ,S]U¦ºV…ªÖ”©®Z¦êoëë·xzã™âÆSnm¬{ÚºwaلÏ…Å»´Ýõ(mg/®þå½À¿¼ûŒ[§b³µq¶Å&Õ¯¹$ñzȊ‹H>aÌKT1/æø1O‚‰0¾.h͇YþAÓö£
-ê>ۋº«¢XÕ¢î}ߨëÛÑ;ÃöN´ØvÅýÎ¸ÿ1ë×ÄO@&v/Äþ_—ö\ôÇ\í.™½+0”;!fÊ¦´Ó% JY·O”ÂŽ'/Å]_Š;ßÀ'"&Nªn	aQ^”cx¦AáÒIEND®B`‚
Hit the pencil icon to edit the table row.

Hints

  • Try to set the first_name to Liz using the technique discussed above.
  • Be careful about the quotation marks: " or '.
  • You can even drop the table if you modify Bobby’s nameRobert’); DROP TABLE students;–”.
  • Hit the RUN button to reset the database.