SQL×¢Èë¹¥»÷µÄÖÖÀàºÍ·À·¶ÊÖ¶Î
¹Û²ì½üÀ´µÄһЩ°²È«Ê¼þ¼°Æäºó¹û£¬°²È«×¨¼ÒÃÇÒѾµÃµ½Ò»¸ö½áÂÛ£¬ÕâЩÍþвÖ÷ÒªÊÇͨ¹ýSQL×¢ÈëÔì³ÉµÄ¡£ËäÈ»Ç°ÃæÓÐÐí¶àÎÄÕÂÌÖÂÛÁËSQL×¢È룬µ«½ñÌìËùÌÖÂÛµÄÄÚÈÝÒ²Ðí¿É°ïÖúÄã¼ì²é×Ô¼ºµÄ·þÎñÆ÷£¬²¢²ÉÈ¡ÏàÓ¦·À·¶´ëÊ©¡£
SQL×¢Èë¹¥»÷µÄÖÖÀà
Öª±ËÖª¼º£¬·½¿Éȡʤ¡£Ê×ÏÈÒªÇå³þSQL×¢Èë¹¥»÷ÓÐÄÄЩÖÖÀà¡£
1.ûÓÐÕýÈ·¹ýÂËתÒå×Ö·û
ÔÚÓû§µÄÊäÈëûÓÐΪתÒå×Ö·û¹ýÂËʱ£¬¾Í»á·¢ÉúÕâÖÖÐÎʽµÄ×¢Èëʽ¹¥»÷£¬Ëü»á±»´«µÝ¸øÒ»¸öSQLÓï¾ä¡£ÕâÑù¾Í»áµ¼ÖÂÓ¦ÓóÌÐòµÄÖÕ¶ËÓû§¶ÔÊý¾Ý¿âÉϵÄÓï¾äʵʩ²Ù×Ý¡£±È·½Ëµ£¬ÏÂÃæµÄÕâÐдúÂë¾Í»áÑÝʾÕâÖÖ©¶´£º
statement := "SELECT * from users WHERE name = '" + userName + "'; "
ÕâÖÖ´úÂëµÄÉè¼ÆÄ¿µÄÊǽ«Ò»¸öÌØ¶¨µÄÓû§´ÓÆäÓû§±íÖÐÈ¡³ö£¬µ«ÊÇ£¬Èç¹ûÓû§Ãû±»Ò»¸ö¶ñÒâµÄÓû§ÓÃÒ»ÖÖÌØ¶¨µÄ·½Ê½Î±Ô죬Õâ¸öÓï¾äËùÖ´ÐеIJÙ×÷¿ÉÄܾͲ»½ö½öÊÇ´úÂëµÄ×÷ÕßËùÆÚÍûµÄÄÇÑùÁË¡£ÀýÈ磬½«Óû§Ãû±äÁ¿(¼´username)ÉèÖÃΪ£º
a' or 't'='t£¬´ËʱÔʼÓï¾ä·¢ÉúÁ˱仯£º
SELECT * from users WHERE name = 'a' OR 't'='t';
Èç¹ûÕâÖÖ´úÂë±»ÓÃÓÚÒ»¸öÈÏÖ¤¹ý³Ì£¬ÄÇôÕâ¸öÀý×Ó¾ÍÄܹ»Ç¿ÆÈÑ¡ÔñÒ»¸öºÏ·¨µÄÓû§Ãû£¬ÒòΪ¸³Öµ't'='tÓÀÔ¶ÊÇÕýÈ·µÄ¡£
ÔÚһЩSQL·þÎñÆ÷ÉÏ£¬ÈçÔÚSQL ServerÖУ¬ÈκÎÒ»¸öSQLÃüÁî¶¼¿ÉÒÔͨ¹ýÕâÖÖ·½·¨±»×¢È룬°üÀ¨Ö´Ðжà¸öÓï¾ä¡£ÏÂÃæÓï¾äÖеÄusernameµÄÖµ½«»áµ¼ÖÂɾ³ý¡°users¡±±í£¬ÓÖ¿ÉÒÔ´Ó¡°data¡±±íÖÐÑ¡ÔñËùÓеÄÊý¾Ý(ʵ¼ÊÉϾÍÊÇ͸¶ÁËÿһ¸öÓû§µÄÐÅÏ¢)¡£
a'; DROP TABLE users; SELECT * from data WHERE name LIKE '%
Õâ¾Í½«×îÖÕµÄSQLÓï¾ä±ä³ÉÏÂÃæÕâ¸öÑù×Ó£º
SELECT * from users WHERE name = 'a'; DROP TABLE users; SELECT * from DATA WHERE name LIKE '%';
ÆäËüµÄSQLÖ´Ðв»»á½«Ö´ÐÐͬÑù²éѯÖеĶà¸öÃüÁî×÷ΪһÏȫ´ëÊ©¡£Õâ»á·ÀÖ¹¹¥»÷Õß×¢ÈëÍêÈ«¶ÀÁ¢µÄ²éѯ£¬²»¹ýÈ´²»»á×èÖ¹¹¥»÷ÕßÐ޸IJéѯ¡£
2.Incorrect type handling
Èç¹ûÒ»¸öÓû§ÌṩµÄ×ֶβ¢·ÇÒ»¸öÇ¿ÀàÐÍ£¬»òÕßûÓÐʵʩÀàÐÍÇ¿ÖÆ£¬¾Í»á·¢ÉúÕâÖÖÐÎʽµÄ¹¥»÷¡£µ±ÔÚÒ»¸öSQLÓï¾äÖÐʹÓÃÒ»¸öÊý×Ö×Ö¶Îʱ£¬Èç¹û³ÌÐòԱûÓмì²éÓû§ÊäÈëµÄºÏ·¨ÐÔ(ÊÇ·ñΪÊý×ÖÐÍ)¾Í»á·¢ÉúÕâÖÖ¹¥»÷¡£ÀýÈ磺
statement := "SELECT * from data WHERE id = " + a_variable + "; "
´ÓÕâ¸öÓï¾ä¿ÉÒÔ¿´³ö£¬×÷ÕßÏ£Íûa_variableÊÇÒ»¸öÓë¡°id¡±×Ö¶ÎÓйصÄÊý×Ö¡£²»¹ý£¬Èç¹ûÖÕ¶ËÓû§Ñ¡ÔñÒ»¸ö×Ö·û´®£¬¾ÍÈÆ¹ýÁ˶ÔתÒå×Ö·ûµÄÐèÒª¡£ÀýÈ磬½«a_vari
Ïà¹ØÎĵµ£º
ÎÊÌâ
ÈçºÎ´´½¨Ò»¸öT-SQL²âÊÔÌ×¼þÓÃÓÚ²âÊÔSQL´æ´¢¹ý³Ì¡£
Éè¼Æ
Ê×ÎÞ£¬Í¨¹ý²åÈë´óÁ¿²âÊÔÆ½Ì¨Êý¾Ý×¼±¸ºÃÒ»¸ö°üº¬´ý²â´æ´¢¹ý³ÌµÄµ×²ãÊý¾Ý¿â¡£½ÓÏÂÀ´£¬Ê¹ÓÃÒ»¸öSQLÓαê(cursor)±éÀúÕâ¸ö²âÊÔÓÃÀýÊý¾Ý±í¡£Õë¶Ôÿ¸ö²âÊÔÓÃÀý£¬µ÷Óôý²â´æ´¢¹ý³Ì²¢ÇÒÈ¡µÃËüµÄ·µ»ØÖµ£ ......
ÓÐÈýÕűí LP_COMPANY, LP_COMPANYTYPE,LP_APPLICATION
SQLÓï¾äÊÇ
SELECT b.appname,c.typename,a.ID, a.APPID, a.PROVID, a.CITY, a.DISTRICT, a.TYPEID, a.PARENTID,
a.COMNAME, a.CHARGER, a.LICENSE, a.OPENBANK, a.ACCOUNT, ......
SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æÐ¡ÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ» ......
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Äã¶¼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâÆªÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ ......
½ñÌ죬ÓÐÒ»¸ösql NOT INÓï¾ä£¬Æ¥ÅäÌõ¼þÀïÓÐÒ»¸önull£¬½á¹ûʲô¶¼²é²»³öÀ´£¬Í¬Ê¾õµÃºÜÄÑÀí½â¡£ÆäʵֻҪÃ÷°×Ò»µã¾Í¿ÉÒÔÁË£¬INÓï¾äÆ¥ÅäµÄʱºòÊÇÓÃ=£¬NOT INÆ¥ÅäµÄʱ»áÓÃ<>£¬¾ÍºÜÈÝÒ×Àí½âÁË¡£
Ê×ÏÈÎÒÃÇÒªÖªµÀ£¬nullÔÚoracleÊǸöÌØÊâµÄ¶«Î÷£¬Ã»ÓÐÈκοɱÈÐÔ£¬Èç¹ûʹÓà =/<> ¶Ô±Ènull£¬µÃµ½µÄʼÖÕÊÇfalse¡£n ......