SQL×¢Èëʽ¹¥»÷
Ò»¡¢Ê²Ã´ÊÇSQL×¢Èëʽ¹¥»÷£¿
ËùνSQL×¢Èëʽ¹¥»÷£¬¾ÍÊǹ¥»÷Õß°ÑSQLÃüÁî²åÈëµ½Web±íµ¥µÄÊäÈëÓò»òÒ³ÃæÇëÇóµÄ²éѯ×Ö·û´®£¬ÆÛÆ·þÎñÆ÷Ö´ÐжñÒâµÄSQLÃüÁî¡£ÔÚijЩ±íµ¥ÖУ¬Óû§ÊäÈëµÄÄÚÈÝÖ±½ÓÓÃÀ´¹¹Ô죨»òÕßÓ°Ï죩¶¯Ì¬SQLÃüÁ»ò×÷Ϊ´æ´¢¹ý³ÌµÄÊäÈë²ÎÊý£¬ÕâÀà±íµ¥ÌرðÈÝÒ×Êܵ½SQL×¢Èëʽ¹¥»÷¡£³£¼ûµÄSQL×¢Èëʽ¹¥»÷¹ý³ÌÀàÈ磺
¢Å ij¸öASP.NET WebÓ¦ÓÃÓÐÒ»¸öµÇ¼ҳÃ棬Õâ¸öµÇ¼ҳÃæ¿ØÖÆ×ÅÓû§ÊÇ·ñÓÐȨ·ÃÎÊÓ¦Óã¬ËüÒªÇóÓû§ÊäÈëÒ»¸öÃû³ÆºÍÃÜÂë¡£
¢Æ µÇ¼ҳÃæÖÐÊäÈëµÄÄÚÈݽ«Ö±½ÓÓÃÀ´¹¹Ô춯̬µÄSQLÃüÁ»òÕßÖ±½ÓÓÃ×÷´æ´¢¹ý³ÌµÄ²ÎÊý¡£ÏÂÃæÊÇASP.NETÓ¦Óù¹Ôì²éѯµÄÒ»¸öÀý×Ó£º
System.Text.StringBuilder query = new System.Text.StringBuilder(
"SELECT * from Users WHERE login = '")
.Append(txtLogin.Text).Append("' AND password='")
.Append(txtPassword.Text).Append("'");
¢Ç ¹¥»÷ÕßÔÚÓû§Ãû×ÖºÍÃÜÂëÊäÈë¿òÖÐÊäÈë"'»ò'1'='1"Ö®ÀàµÄÄÚÈÝ¡£
¢È Óû§ÊäÈëµÄÄÚÈÝÌá½»¸ø·þÎñÆ÷Ö®ºó£¬·þÎñÆ÷ÔËÐÐÉÏÃæµÄASP.NET´úÂë¹¹Ôì³ö²éѯÓû§µÄSQLÃüÁµ«ÓÉÓÚ¹¥»÷ÕßÊäÈëµÄÄÚÈݷdz£ÌØÊ⣬ËùÒÔ×îºóµÃµ½µÄSQLÃüÁî±ä³É£ºSELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'¡£
¢É ·þÎñÆ÷Ö´Ðвéѯ»ò´æ´¢¹ý³Ì£¬½«Óû§ÊäÈëµÄÉí·ÝÐÅÏ¢ºÍ·þÎñÆ÷Öб£´æµÄÉí·ÝÐÅÏ¢½øÐжԱȡ£
¢Ê ÓÉÓÚSQLÃüÁîʵ¼ÊÉÏÒѱ»×¢Èëʽ¹¥»÷Ð޸ģ¬ÒѾ²»ÄÜÕæÕýÑéÖ¤Óû§Éí·Ý£¬ËùÒÔϵͳ»á´íÎóµØÊÚȨ¸ø¹¥»÷Õß¡£
Èç¹û¹¥»÷ÕßÖªµÀÓ¦ÓûὫ±íµ¥ÖÐÊäÈëµÄÄÚÈÝÖ±½ÓÓÃÓÚÑéÖ¤Éí·ÝµÄ²éѯ£¬Ëû¾Í»á³¢ÊÔÊäÈëijЩÌØÊâµÄSQL×Ö·û´®´Û¸Ä²éѯ¸Ä±äÆäÔÀ´µÄ¹¦ÄÜ£¬ÆÛÆϵͳÊÚÓè·ÃÎÊȨÏÞ¡£
ϵͳ»·¾³²»Í¬£¬¹¥»÷Õß¿ÉÄÜÔì³ÉµÄËðº¦Ò²²»Í¬£¬ÕâÖ÷ÒªÓÉÓ¦Ó÷ÃÎÊÊý¾Ý¿âµÄ°²È«È¨ÏÞ¾ö¶¨¡£Èç¹ûÓû§µÄÕÊ»§¾ßÓйÜÀíÔ±»òÆäËû±È½Ï¸ß¼¶µÄȨÏÞ£¬¹¥»÷Õ߾ͿÉÄܶÔÊý¾Ý¿âµÄ±íÖ´Ðи÷ÖÖËûÏëÒª×öµÄ²Ù×÷£¬°üÀ¨Ìí¼Ó¡¢É¾³ý»ò¸üÐÂÊý¾Ý£¬ÉõÖÁ¿ÉÄÜÖ±½Óɾ³ý±í¡£
¶þ¡¢ÈçºÎ·À·¶£¿
ºÃÔÚÒª·ÀÖ¹ASP.NETÓ¦Óñ»SQL×¢Èëʽ¹¥»÷´³Èë²¢²»ÊÇÒ»¼þÌرðÀ§ÄѵÄÊÂÇ飬ֻҪÔÚÀûÓÃ±íµ¥ÊäÈëµÄÄÚÈݹ¹ÔìSQLÃüÁî֮ǰ£¬°ÑËùÓÐÊäÈëÄÚÈݹýÂËÒ»·¬¾Í¿ÉÒÔÁË¡£¹ýÂËÊäÈëÄÚÈÝ¿ÉÒÔ°´¶àÖÖ·½Ê½½øÐС£
¢Å ¶ÔÓÚ¶¯Ì¬¹¹ÔìSQL²éѯµÄ³¡ºÏ£¬¿ÉÒÔʹÓÃÏÂÃæµÄ¼¼Êõ£º
µÚÒ»£ºÌæ»»µ¥ÒýºÅ£¬¼´°ÑËùÓе¥¶À³öÏֵĵ¥ÒýºÅ¸Ä³ÉÁ½¸öµ¥ÒýºÅ£¬·ÀÖ¹¹¥»÷ÕßÐÞ¸ÄSQLÃüÁîµÄº¬Òå¡£ÔÙÀ´¿´Ç°ÃæµÄÀý×Ó£¬"SELECT * from Users WHERE login = ''' or
Ïà¹ØÎĵµ£º
1¡¢ÒªÆôÓÃÈ«ÎÄË÷Òý¹¦ÄÜÊ×ÏÈÐèÒª°²×°full text searchÈ«ÎÄË÷Òý·þÎñ
2¡¢Æô¶¯full text search·þÎñ
3¡¢ÏÈ´´½¨UniqueË÷ÒýºÍÈ«ÎÄË÷Òý£ºCREATE FULLTEXT INDEX ON table_name
4¡¢Ã¿¸ö±íÖ»ÔÊÐí´´½¨Ò»¸öÈ«ÎÄË÷Òý
ɾ³ýÈ«ÎÄË÷Òý DROP FULLTEXT INDEX ON table_name
È«ÎÄËÑË÷Óï¾ä£¬contains(),freeText()
×¢ ......
SQL Server ¼¸¸öºÃÓõÄSQLÓï¾ä
1¡¢¸´ÖƱí
select * into desttable from srctable
½« srctable ÍêÕûµØ¸´ÖÆÒ»·Ýµ½ desttable ÖУ¬µ±È»ºóÃæÒ²¿ÉÒÔ¼ÓÉÏÌõ¼þÀ´ÏÞ¶¨ÐèÒª¸´ÖƵļǼ
ÒªÇó£ºdesttable ±ØÐëΪ²»´æÔڵıíÃû¡£
insert into desttable(column1, column2) select columna, columnb from sr ......
Ñ¡Ôñ10gÊÇÒòΪ¶ÔÍø¸ñ¼¼Êõ±È½ÏºÃÆ棬ºÃÏñ»¹Ã»µÃµ½¹ã·ºÓ¦Óã¬Ò²¾ÍÎÞËùνÓëÇ°Ãæ°æ±¾ÓкܴóÇø±ðÁË¡£
ÔÚѸÀ×ÉÏÏÂÁ˸öÈí¼þ£¬ÔËÐÐsqlplusw£¬È»ºóÕÕ×ÅÊ飬¿ñÇÃÁËÒ»·£¬ÓÐÔÚÍøÉÏÏÂÁ˸öÊÓƵ½Ì³Ì£¨MLDNħÀֿƼ¼_Oracle¿ÎÌã©£¬½²µÃͦºÃµÄ£¬¾ÍÊÇʱ¼ä³¤Á˵㣬²»Èç¿´ÊéÀ´µÃ¿ì¡£·´ÕýÏÖÔÚÖ»ÊÇÏëÊìϤһÏ»ù±¾Óï¾ä¡£
¿´ÁËÁ½ÌìÊéÁË¡£Á˽âÁËÒ» ......
´Ë²¿·ÖÄÚÈÝ´´½¨Ò»¸öÇáÁ¿¼¶T-SQL²âÊÔÌ×¼þ£¬×ܹ²ÓÐ3¸ö½Å±¾£º
ÓÃÓÚ´´½¨²âÊÔƽ̨Êý¾ÝºÍ´ý²â´æ´¢¹ý³ÌµÄ½Å±¾
--======================
--makeDbTestAndResults.sql
use master
go
if exists (select * from sysdatabases where name = 'DbTestAndResults')
drop database makeDbTes ......
´ÓÕâÒ»½Ú¿ªÊ¼ÄØ£¬ÎÒÃǾÍÒª¿ªÊ¼CLRµÄ±à³ÌÖ®ÂÃÁË¡£ÔÚÕâ֮ǰ£¬ÎÒÏȰѱ¾½ÚÖÐÐèÒªÁ˽âµÄÁ½¸öÐÂÀàSqlDataRecordºÍSqlMetaData£¬¼°Îå¸öз½·¨SqlContext.Pipe.SendResultsStart£¬SqlContext.Pipe.SendResultsRow£¬SqlContext.Pipe.SendResultsEnd£¬SqlContext.Pipe.SendºÍSqlContext.Pipe.ExecuteAndSend½øÐÐһϱØÒªµÄ˵Ã÷£¬·½±ã´ ......