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
Ïà¹ØÎĵµ£º
ÊìϤSQL SERVER 2000µÄÊý¾Ý¿â¹ÜÀíÔ±¶¼ÖªµÀ£¬ÆäDTS¿ÉÒÔ½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬Æäʵ£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃTransact-SQLÓï¾ä½øÐе¼Èëµ¼³ö²Ù×÷¡£ÔÚTransact-SQLÓï¾äÖУ¬ÎÒÃÇÖ÷ҪʹÓÃOpenDataSourceº¯Êý¡¢OPENROWSET º¯Êý£¬¹ØÓÚº¯ÊýµÄÏêϸ˵Ã÷£¬Çë²Î¿¼SQLÁª»ú°ïÖú¡£ÀûÓÃÏÂÊö·½·¨£¬¿ÉÒÔÊ®·ÖÈÝÒ×µØÊµÏÖSQL SERVER¡¢ACCESS¡¢EXCELÊý¾Ýת»»£ ......
ÎÊÌâ
ÈçºÎ´´½¨Ò»¸öT-SQL²âÊÔÌ×¼þÓÃÓÚ²âÊÔSQL´æ´¢¹ý³Ì¡£
Éè¼Æ
Ê×ÎÞ£¬Í¨¹ý²åÈë´óÁ¿²âÊÔÆ½Ì¨Êý¾Ý×¼±¸ºÃÒ»¸ö°üº¬´ý²â´æ´¢¹ý³ÌµÄµ×²ãÊý¾Ý¿â¡£½ÓÏÂÀ´£¬Ê¹ÓÃÒ»¸öSQLÓαê(cursor)±éÀúÕâ¸ö²âÊÔÓÃÀýÊý¾Ý±í¡£Õë¶Ôÿ¸ö²âÊÔÓÃÀý£¬µ÷Óôý²â´æ´¢¹ý³Ì²¢ÇÒÈ¡µÃËüµÄ·µ»ØÖµ£ ......
package com.itcast.service.base;
import java.util.LinkedHashMap;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.transaction.annotation.Propagation;
import org.spri ......
¹ØÓÚDBAµÄ½ÇÉ«
Éú²úDBA
ÊôÓÚ´«Í³DBA½ÇÉ«£¬Êǹ«Ë¾µÄ‘±£µ¥’£¬È·±£Éú²úÊý¾Ý²»»á³ö´í¡£»¹¸ºÔðÈ·±£·þÎñÆ÷Ò»×îÓÅ»¯µÄ·½Ê½ÔËÐУ¬²¢´Ù½øÊý¾Ý¿â´Ó¿ª·¢ÆÚתµ½QAÔÙתµ½Éú²ú¡£
Éú²úDBAµÄÆäËûһЩ½ÇÉ«°üÀ¨ÒÔÏ·½Ã棺
1£© °²×°SQL Server ʵÀýºÍ²¹¶¡°ü¡£
2£© ¼àÊÓÐÔÄÜÎÊÌâ¡£
3£© °²×°À´×Ô¿ª·¢ÈËÔ±µÄ½ ......
½ñÌ죬ÓÐÒ»¸ösql NOT INÓï¾ä£¬Æ¥ÅäÌõ¼þÀïÓÐÒ»¸önull£¬½á¹ûʲô¶¼²é²»³öÀ´£¬Í¬Ê¾õµÃºÜÄÑÀí½â¡£ÆäʵֻҪÃ÷°×Ò»µã¾Í¿ÉÒÔÁË£¬INÓï¾äÆ¥ÅäµÄʱºòÊÇÓÃ=£¬NOT INÆ¥ÅäµÄʱ»áÓÃ<>£¬¾ÍºÜÈÝÒ×Àí½âÁË¡£
Ê×ÏÈÎÒÃÇÒªÖªµÀ£¬nullÔÚoracleÊǸöÌØÊâµÄ¶«Î÷£¬Ã»ÓÐÈκοɱÈÐÔ£¬Èç¹ûʹÓà =/<> ¶Ô±Ènull£¬µÃµ½µÄʼÖÕÊÇfalse¡£n ......