当前位置：首页 > 实用文档 > 其他范文> 对于跨不同服务器的sql脚本执行语言的摘要数据库教程

对于跨不同服务器的sql脚本执行语言的摘要数据库教程

时间：2023-08-05 08:09:15 其他范文收藏本文下载本文

对于跨不同服务器的sql脚本执行语言的摘要数据库教程（共5篇）由网友“呆瓜只会卖菜”投稿提供，下面是小编为大家整理后的对于跨不同服务器的sql脚本执行语言的摘要数据库教程，欢迎阅读与收藏。

篇1：对于跨不同服务器的sql脚本执行语言的摘要数据库教程

服务器|脚本|执行

对于相关的数据库脚本的手动执行的数据互导功能！

关键的重点在于建立的连接关系，连接建立好后执行的数据库相关的脚本实现就方便的多了~~~~

1，从一个数据库的一张表B中向另外的一个数据库的表A（起码有相同的字段属性，或字段值）

insert into A(字段1，字段2。。。字段n)

select 字段1，字段2，120，...字段n

from OPENDATASOURCE('SQLOLEDB','Data Source=B所在的服务器;User ID=用户名;Password=登陆口令').B库.dbo.B表

where 字段1=？ and ...

该操作一般用在查询分析器中！

2。游标的利用，在服务器间传递数据的值

declare @GEN_ID nvarchar(4000)

declare @QUAN nvarchar(4000)

declare @FEE_STATUS_OPERATOR nvarchar(4000)

declare @FEE_STATUS_OPERATETIME nvarchar(4000)

declare @status_operateid nvarchar(4000)

declare @client_sname nvarchar(4000)

BEGIN TRAN STATUS

declare USR cursor for select d.字段1,d.字段2 from OPENDATASOURCE(

'SQLOLEDB',

'Data Source=服务器1;User ID=;Password='

).库1.dbo.表1 as g inner join OPENDATASOURCE(

'SQLOLEDB',

'Data Source=服务器1;User ID=;Password='

).库1.dbo.表1 as d on g.字段1=d.字段1 inner join OPENDATASOURCE(

'SQLOLEDB',

'Data Source=服务器1;User ID=;Password='

).库2.dbo.表1‘ as s on g.字段1=s.字段1 where g.字段2=0 and g.字段3=0 and s.字段4=6

open USR

fetch next from USR into @GEN_ID,@QUAN

WHILE @@FETCH_STATUS = 0

BEGIN

update 库A.dbo.表 set 字段=@QUAN where 字段1=rtrim(@GEN_ID)

fetch next from USR into @GEN_ID,@QUAN

end

close USR

deallocate USR

declare USR1 cursor for select 字段1，字段2，120，...字段n

from OPENDATASOURCE(

'SQLOLEDB',

'Data Source=服务器1;User ID=;Password='

).库1.dbo.表1 as g inner join OPENDATASOURCE(

'SQLOLEDB',

'Data Source=服务器1;User ID=;Password='

).库1.dbo.表1 as d on g.字段1=d.字段1

where g.字段2=0 and g.字段3=0 and s.字段4=6

open USR1

fetch next from USR into @GEN_ID,@FEE_STATUS_OPERATOR,@FEE_STATUS_OPERATETIME,@status_operateid,@client_sname

WHILE @@FETCH_STATUS = 0

BEGIN

insert into A(字段1，字段2，

。。字段n)

values(@GEN_ID,'2','0',@FEE_STATUS_OPERATOR,@FEE_STATUS_OPERATETIME,@status_operateid,@client_sname)

fetch next from USR1 into @GEN_ID,@FEE_STATUS_OPERATOR,@FEE_STATUS_OPERATETIME,@status_operateid,@client_sname

end

close USR1

deallocate USR1

----------------说明对于游标的利用可以多次，唯一的缺点的就是执行的时间过长！对于这方面要权衡考虑！

if (@@error0)

begin

rollback tran STATUS

end

else

begin

commit tran STATUS

end

------------事物处理

篇2：Oracle 和 MIcrosoft SQL 的不同数据库教程

oracle

还是有很多的不同,如下:www.bristle.com/Tips/SQL.htm#Oracle%20Tips

Table of Contents:Oracle Tips SQL Tips SELECT * and more Materialized View PL/SQL Tips SQL Navigator Tips See Also MS SQL Server Tips SQL Tips Dynamic SQL in a Stored Procedure SQL Enterprise Manager Tips Keyboard Shortcuts SQL Generating SQL See Also Differences Between Oracle and MS SQL Server Concepts and Terminology Data Types Limits Operators Built-In Functions Differences in SQL Syntax Differences in SQL Semantics Differences in Managing Databases Differences in Managing Database Objects Differences in Managing Users Differences in Integration with MS ADO, RDO, etc. Miscellaneous Differences See Also Details of Tips:Oracle TipsSQL Tips

This section contains tips on standard SQL (Structured Query Language) statements in Oracle.

SELECT * and more

Last Updated: 6/6/

Applies to: Oracle 7.3, 8 (and probably earlier versions)

To select all columns of a table:

select * from table

However, to select all real columns, plus a pseudo-column like “user”:

select table.*, user from table

The following does not work:

select *, user from table

--Fred

Materialized View

Last Updated: 1/7/

Applies to: Oracle 8+

Oracle 8i introduced a new feature called a “materialized view”. You define it just like any other view, except that you add the keyword MATERIALIZED:

CREATE MATERIALIZED VIEW view_name

A materialized view is like a combination of a table and a view. Like a view, it is defined as a logical view into the data of one or more tables. When you update the tables, subsequent queries of the view see the updated data. However, like a table, its data is stored in the database. Also, like a table, it is faster if you define indexes for it.

A regular view is stored as a mapping of data from tables. When you modify the data in the tables, the view is completely ignored. When you access the view, it joins the data currently in the tables, and returns the data you requested. A materialized view is stored as such a mapping along with a copy of the actual data from the tables. When you modify the data in the tables, the view's copy of the data is also updated. When you access the view, the data is drawn directly from the copy.

Thus a materialized view makes table updates a little slower, but makes view queries much faster. It also consumes additional space in the database.

You could accomplish the same effect by defining an additional table instead of the view, and using triggers on the component tables to update it each time they are changed. However, using a materialized view is more convenient, more efficient, and clearer to the next person who has to maintain your database.

Thanks to Andy Glick for sending me a sample of a materialized view from his application!

--Fred

PL/SQL Tips

This section contains tips on PL/SQL statements -- the Oracle “procedural language” superset of SQL that you use to write stored procedures.

SQL Navigator Tips

This section contains tips on the SQL Navigator tool by Quest Systems. It is a graphical front end to the Oracle database, allowing you to create, delete, view, and modify all Oracle objects: tables, views, stored procedures, etc.

See Also

Last Updated: 6/6/1999

Applies to: MS SQL Server 6.5+

The following are good sources of info about MS SQL Server:

MS SQL Server books on the MSDN Library CD.

--Fred

Differences Between Oracle and MS SQL ServerConcepts and Terminology

Last Updated: 4/24/

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows some differences in concepts and terminology between Oracle and MS SQL Server:

Concept/TermOracleMS SQL ServerDatabase enginedatabasedatabase serverDatabase (collection of tables)schemadatabaseRoles/GroupsrolesgroupsDatabase adminstrator account, database ownerdbasa, dboData about the databaseData Dictionary

- one per serverDatabase Catalog

- one per database

“master” database

- one per serverBlocks and extentsblocks and extentspages and extentsNetwork softwareSQL*NetNet-libraryData stream protocolTransparent Network Substrate (TNS)Tabular Data Stream (TDS) Case sensitivity of names of tables, columns, etc.case-insensitivedepends on character sort order, default is case-insensitiveSynonymssupportednot supportedReadonly transactionsupportednot supported

--Fred

Data Types

Last Updated: 6/6/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows the corresponding data types in Oracle and MS SQL Server:

Data TypeOracleMS SQL ServerFixed Length StringCHAR(n)

- limit 2KBCHAR(n), CHARACTER(n)

- limit 255 (6.5)

- limit 8KB (7.0)Variable Length StringVARCHAR2(n), VARCHAR(n)

- limit 4KB in a column

- limit 32KB in a variable

- VARCHAR is obsolete

VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

- limit 255 (6.5)

- limit 8KB (7.0)IntegerINTEGER, INTEGER(n), SMALLINTINTEGER (4 bytes),

INT (4 bytes),

SMALLINT (2 bytes),

TINYINT (1 byte),

BIT (1 bit)Fixed PointNUMBER, NUMBER(n), NUMBER(n,d),

FLOAT, FLOAT(n), FLOAT(n,d)NUMERIC, NUMERIC(n), NUMERIC(n,d),

DECIMAL, DECIMAL(n), DECIMAL(n,d),

DEC, DEC(n), DEC(n,d),

MONEY, SMALLMONEYFloating PointDECIMALFLOAT, FLOAT(n), DOUBLE PRECISION,

REAL, DateDATEDATETIME, SMALLDATETIME, TIMESTAMP

- TIMESTAMP auto-updatedBinaryRAW(n)

- limit 255 bytesBINARY(n), VARBINARY(n), BINARY VARYING(n)

- limit 255 (6.5)

- limit 8KB (7.0)Large StringLONG, LONG VARCHAR

- limit 2GB

- limit one per table row

CLOB

- limit 4GBTEXT

- limit 2GBLarge BinaryLONG RAW

- limit 2GB

- limit one per table row

BLOB

- limit 4GBIMAGE

- limit 2GBMulti-byte charsNCHAR(n)

NVARCHAR(n)

NCLOB

- same limits as CHAR, VARCHAR, CLOBNCHAR(n), NATIONAL CHAR(n), NATIONAL CHARACTER(n)

NVARCHAR(n), NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n)

NTEXT, NATIONAL TEXT

- same limits as CHAR, VARCHAR, TEXTOS FileBFILERow Identifierimplicit ROWID column(use an IDENTITY column)Secure OS LabelMLSLABEL, RAW MLSLABEL128-bit Unique Number

(UUID, GUID) UNIQUEIDENTIFIER (version 7.0 only)

--Fred

Limits

Last Updated: 6/14/

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows differences in limits of Oracle and MS SQL Server:

DescriptionOracleMS SQL ServerColumns per table1000250 (6.5)

1024 (7.0)Row size unlimited1962 bytes (6.5)

8060 bytes (7.0)

- includes pointers, but not data, for TEXT and IMAGE columnsLONG and LONG RAW columns per row1 (must be last column)unlimited (16-byte pointer per)LOB, TEXT, and IMAGE columns per rowunlimited (16-byte pointer per)unlimited (16-byte pointer per)Clustered indexes per table11Non-clustered indexes per tableunlimited249Columns per index1616Index row size2K bytes900 bytesIdentifier Length30 chars30 chars (6.5)

128 chars (7.0)Tables per SELECTunlimited16 (6.5)

256 (7.0)Source code per stored procedure 64KB (6.5)

250MB (7.0)Data type limits(see Data Types)

--Fred

Operators

Last Updated: 6/7/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

Most operators are the same in Oracle and MS SQL Server. Here are some that differ:

DescriptionOracleMS SQL ServerString concatenationstring1 || string2string1 + string2

--Fred

Built-In Functions

Last Updated: 6/7/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

Oracle and MS SQL Server offer many of the same built-in functions. For example, they both offer ABS, EXP, ROUND, UPPER, LOWER, AVG, COUNT, SUM, ASCII, etc. The following table shows some of the corresponding functions that don't have the same name. For a more complete list, see “Migrating Oracle Applications to SQL Server”

DescriptionOracleMS SQL ServerSmallest integer >= nCEILCEILINGModulusMOD%Truncate numberTRUNCMax or min number or string in listGREATEST,

LEASTTranslate NULL to nNVLISNULLReturn NULL if two values are equalDECODENULLIFString concatenationCONCAT(str1,str2)str1 + str2Convert ASCII to charCHRCHARCapitalize first letters of wordsINITCAPFind string in stringINSTRCHARINDEXFind pattern in stringINSTRPATINDEXString lengthLENGTHDATALENGTHPad string with blanksLPAD,

RPADTrim leading or trailing chars other than blanksLTRIM(str,chars),

RTRIM(str,chars)Replace chars in stringREPLACESTUFFConvert number to stringTO_CHARSTR, CASTConvert string to numberTO_NUMBERCASTGet substring from stringSUBSTRSUBSTRINGChar for char translation in stringTRANSLATEDate additionADD_MONTH or +DATEADDDate subtractionMONTHS_BETWEEN or -DATEDIFFLast day of monthLAST_DAYTime zone conversionNEW_TIMENext specified weekday after dateNEXT_DAYConvert date to stringTO_CHARDATENAME, CONVERTConvert string to dateTO_DATECASTConvert date to numberTO_NUMBER(TO_CHAR(d))DATEPARTDate roundROUNDCONVERTDate truncateTRUNCCONVERTCurrent dateSYSDATEGETDATEConvert hex to binaryHEXTORAWCASTConvert binary to hexRAWTOHEXCONVERTIf statement in an expressionDECODECASE ... WHEN

or COALESCEUser's login id number or nameUID, USERSUSER_ID, SUSER_NAMEUser's database id number or nameUID, USERUSER_ID, USER_NAMECurrent userUSERUSER

--Fred

Differences in SQL Syntax

Last Updated: 3/21/2001

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows the different syntax used in Oracle and MS SQL Server for the same SQL operations:

DescriptionOracleMS SQL ServerLeft Outer JoinWHERE column1 = column2(+)FROM table1 LEFT OUTER JOIN table2 ON table1.column1 = table2.column2

Note: The following syntax is also supported, but is no longer recommended:

WHERE column1 *= column2Right Outer JoinWHERE column1(+) = column2FROM table1 RIGHT OUTER JOIN table2 ON table1.column1 = table2.column2

Note: The following syntax is also supported, but is no longer recommended:

WHERE column1 =* column2Full Outer Join FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column2SELECT without FROMSELECT 'hello world' FROM DUALSELECT 'hello world'SELECT data into a tableCREATE TABLE AS SELECT ...SELECT ... INTOIntersection of 2 SELECTSSELECT ... INTERSECT SELECT ...SELECT ... WHERE EXISTS (SELECT ...)Subtraction of 2 SELECTSSELECT ... MINUS SELECT ...SELECT ... WHERE NOT EXISTS (SELECT ...)INSERT into a JOININSERT INTO SELECT ...Create a VIEW and INSERT INTO it.UPDATE data in a JOINUPDATE SELECT...Create a VIEW and INSERT INTO it.UPDATE one table based on criteria in another tableUPDATE table FROM ...DELETE rows from one table based on criteria in another tableDELETE FROM table FROM ...DROP a column from a tableALTER TABLE table_name DROP COLUMN column_nameReadonly VIEWCREATE VIEW ... WITH READONLYGRANT SELECT ...Save pointSAVEPOINTSAVE TRANSACTIONTable lockLOCK TABLE...IN SHARE MODE SELECT...table_name (TABLOCK)Exclusive table lockLOCK TABLE...IN EXCLUSIVE MODE SELECT...table_name (TABLOCKX)Reserving index space PCTFREE=0FILLFACTOR=100Declaring a local variableDECLARE varname type;DECLARE @varname typeInitializing a local variableDECLARE varname type := value;Declaring a constantDECLARE varname CONSTANT type := value;Assigning to a variablevarname := value

SELECT value INTO varnameSET @varname = value

SELECT @varname = valueAssigning to a variable from a cursorFETCH cursorname INTO varnameFETCH NEXT FROM cursorname INTO varnameDeclaring a cursorCURSOR curname (params)

IS SELECT ...;DECLARE curname CURSOR FOR SELECT ...If statementIF ... THEN

ELSIF ... THEN

ELSE

ENDIFIF ...

BEGIN ... END

ELSE BEGIN ... ENDWhile loopWHILE ... LOOP

END LOOPWHILE ...

BEGIN ... ENDOther loopsFOR ... END LOOP

LOOP ... END LOOPLoop exitEXIT, EXIT WHENBREAK, CONTINUEPrint outputDBMS_OUTPUT.PUT_LINEPRINTRaise errorRAISE_APPLICATION_ERRORRAISERRORStatement terminatorSemi-colon (;)

Thanks to Tom Johnston for catching a mistake in this tip. I had the FROM DUAL in the wrong column.

--Fred

Differences in SQL Semantics

Last Updated: 6/6/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows some semantic differences between Oracle and MS SQL Server:

DescriptionOracleMS SQL ServerCommitExplicit COMMIT statement requiredAutomatic commit unless SET IMPLICIT_TRANSACTIONS ONReading uncommitted dataDatabase does temporary internal rollback to reconstruct most recently committed data for reader.Depending on options, reader as allowed to read uncommitted data, or is forced to wait for writer to commit or rollback.Releasing cursor dataCLOSE CURSOR releases all data. You can't re-open.CLOSE CURSOR does not release data. You must explicitly call DEALLOCATE CURSOR. Until then, you can re-open the cursor.Implicit data conversion in a statement like the following where vc is a column of type VARCHAR2:

SELECT * FROM person

WHERE vc =123

As each row is fetched from the table, an attempt is made to convert it to a number for the comparison with 123. If any row contains a value that cannot be converted to a number, a runtime error occurs.The number 123 is converted to the string '123' once, and then the data is fetched from the table. If any row contains a value that cannot be converted to a number, it simply doesn't match '123' and is skipped without any error.Conversion to NULLSetting a VARCHAR2 column to '' (the empty string) makes it NULL.Setting a VARCHAR column to '' makes it the empty string (not NULL).

--Fred

Differences in Managing Databases

Last Updated: 6/6/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows some differences in how databases are managed in Oracle and MS SQL Server:

DescriptionOracleMS SQL ServerModel databaseNo model databaseNewly created databases inherit characteristics (users, etc.) from the special database named “model”.

--Fred

Differences in Managing Database Objects

Last Updated: 6/6/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows some differences in how database objects (tables, views, stored procedures, etc.) are managed in Oracle and MS SQL Server:

DescriptionOracleMS SQL ServerFully qualified name[schema.]table

[schema.]view[[[server.][database].][owner].]table

[[[server.][database].][owner].]viewTemp tablesPre 8i: Temporary tables must be deleted explicitly

8i+: CREATE GLOBAL TEMPORARY TABLE

#table -- Any table named starting with a pound sign (#) is automatically deleted when the user logs off or the procedure ends.

##table -- Same as above, except that the table is accessible to other users.Re-creating an objectCREATE OR REPLACE ...DROP ...

CREATE ...Create view before dependent tables CREATE FORCE VIEWNot supported. Tables used by view must exist before view can be created.

--Fred

Differences in Managing Users

Last Updated: 6/6/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows some differences in how users are managed in Oracle and MS SQL Server:

DescriptionOracleMS SQL ServerMembership in groupsEach user can be a member of any number of groups.Each user can be a member of only one group other than “public”.

--Fred

Differences in Integration with MS ADO, RDO, etc.

Last Updated: 6/6/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows the different techniques used in Oracle and MS SQL Server to interact with MS ADO, RDO, etc.:

DescriptionOracleMS SQL ServerReturn a recordset to the callerReturn a handle to a cursor.

For more info: See MS KB article Q174679.SELECT with no INTO clause;

Multiple such SELECTs return multiple recordsets

--Fred

Miscellaneous Differences

Last Updated: 6/6/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows miscellaneous differences between Oracle and MS SQL Server:

DescriptionOracleMS SQL ServerGenerate unique numbersCREATE SEQUENCEIDENTITY column of a tableCascaded DELETEDELETE CASCADE ...(use triggers)Call a user-defined function from a SQL statement (as column of SELECT or expression in WHERE clause)supportednot supported

--Fred

篇3：UBB的跨站脚本攻击的漏洞服务器教程

ubb|攻击|脚本

近日,由于发现一些站点仍然存在UBB的跨站脚本攻击的漏洞.跨站脚本攻击虽然很少会对服务器造成一些什么比较大的影响,但对于一个站点来说,存在这种漏洞实在是太不值得!小则,弹点什么东东出来;中则改改主页;重则窃取用户的COOKIES资料,更甚者将会G掉浏览者的硬盘.一个站点被变成一个恶意网站,还有谁敢来?如果再加上该站的站长比较“盲”一些,岂不乱套了?

小小的一段代码就真的能使一个站点成这样?好叫我们来具体的看看所谓的跨站脚本攻击到底会成为什么样的攻击模式.进入一个含UBB功能的站点，比如留言板,论坛，或是含提交程序的站点.首先,讲一下最简单的脚本攻击:等HTML字符的过滤问题.

登陆过一个CGI制作站点.以原来ASP的眼光看CGI的站点,感觉CGI对脚本的过滤应该很好.于是做了最初的测试.在用户一栏中填写,提交用户注册后发现并没提出非法字符的提示.注册完成后,点击资料也发现页面变形了.如在其他几个如国家,性别里填写也会出现同样的问题,那页面就没法看了.于是换了一个站点，再次提交出现了非法字符提示,看来站点是已经过滤的等HTML的脚本字符,那好，我们改用ASCII 码替换如& #60; & #62;代替提交后再来看，又出现了上面页面变形的情况，看来非法过滤机制还不是很完善. 更有甚者,我竟发现一个站点内的姓名栏填写时没有字数大小设置,没有过滤任何非法字符,如果我提交个什么恶意代码那还不成全了我?

简单的脚本攻击如等HTML格式的代码一定要过滤好,上面的一些站点还没牵扯到UBB就出问题了.那我们下面就开始重点讲一下UBB过滤漏洞的问题.UBB是论坛中用来替换HTML编辑的一种格式符号,如[b ][/b ]可以替换成HTML中的< b>..然而就是这一个替换问题,就成了跨站脚本攻击的最佳积聚地.测试了一个ASP留言版以及一个整站的程序代码:

初级问题: [url ]的过滤,在提交该代码时可以构建一个onmouseover函数的恶意代码,既然onmouseover可以生效,那还有什么办不到的？一些原码程序在变换[url ][/url ]时,只是将[url ]s2[/ url]中的s2直接提交到< a href=“s2” target=_blank>S2中.看到如此转换我们可以使用相应的ASCII 码中的& #34;来代替“,我们多提交一个”然后在构建onmouseover函数进行操作,后果?你应该知道!:P

[img ]的过滤，这真的算是个老大难的问题.很早以前就是[img]的脚本攻击流行一时啊.这次测试中,很多站点还是仍然存在这个漏洞.有点程序根本没有进行过滤.尤其是一些免费留言板的提供站点很多都存在这样的问题.下面我们主要将一下[IMG ]标签的问题:

很简单的[img ]javascript.:alert;[/ img]提交后转换成的代码为< img src=“javascript.:alert();”>,好，到这里我们就可以看到javascript.:alert();被< img src=“”>标签激活了.表现就是弹出对话框.上面写着你在()中要提交的东西.如documents.cookie 呵呵..大多数人都应该知道这东西是做什么.更有甚者,使用document.write();函数,可以写网页.写什么?当然是恶意代码,如[ img]javascript.:document.write();[ /img].()中间加你要加的，写你要写的，想多危险就多危险.

高级问题: 由于[img ]的初级问题骚扰很多站点就对一个敏感的字符开始过滤.如ja连接,do连接,wr连接,提交后自动分为j a,d o,w r.或是对字符进行过滤java,document,等等.而这些只能难倒一小部分人.我们仍然可以利用ASCII码来代替.可能有人会对代替后的代码仍然不能正常显示而困惑.好,我们下面以一个完整的例子介绍:

某站点UBB过滤代码段如下:

Function code_ssstrers)

dim strer:strer=strers

if strer=“” or isnull(strer) then code_ss“”:exit function

strer=replace(strer,“<”,“<”)

strer=replace(strer,“>”,“>”)

strer=replace(strer,“ ”,“ ”) '空格

strer=replace(strer,CHR(9),“ ”) 'table

strer=replace(strer,“'”,“'”) '单引号

strer=replace(strer,“”“”,“”“) '双引号

dim re,re_v

re_v=”[^\;\';“”\[]*“

're_v=”.[^\[]*“

Set re=new RegExp

re.IgnoreCase =True

re.Global=True

re.Pattern=”(javascript.:)“

strer=re.Replace(strer,”javascript：“)

re.Pattern=”(javascript)“

strer=re.Replace(strer,”javascript“)

re.Pattern=”(jscript.:)“

strer=re.Replace(strer,”jscript.:“)

re.Pattern=”(js:)“

strer=re.Replace(strer,”js:“)

re.Pattern=”(value)“

strer=re.Replace(strer,”value“)

re.Pattern=”(about:)“

strer=re.Replace(strer,”about:“)

re.Pattern=”(file:)“

strer=re.Replace(strer,”file&:“)

re.Pattern=”(document.)“

strer=re.Replace(strer,”document :“)

re.Pattern=”(vbscript.:)“

strer=re.Replace(strer,”vbscript.:“)

re.Pattern=”(vbs:)“

strer=re.Replace(strer,”vbs :“)

re.Pattern=”(on(mouse|exit|error|click|key))“

strer=re.Replace(strer,”on$2“)

能看懂ASP的朋友,就可以看出，以上代码段对javascript,jscript.:,js:,about;value,document.,onmouse以及onexit等语句进行了过滤和替换.并对一些特殊字符进行了替换.如”.“,”;“”(“,”)“ [替换代码为”“中间的”..仔细观察代码后我们会发现其过滤机制并非想想象的那样完美.提交:[ mg]& #176& #93& #118& #97& #115& #79rip& #106& #57documen& #115& #76write& #30& #29哈哈又被黑了& #29& #61& #29[/ mg] 类似代码就可以实现更高级的脚本攻击.注:由于很多站点仍存在此问题，所以将代码修改过，无攻击性了.

打开你的FT2000,使用文本编辑,你可以找到任何特殊字符所属的ASCII码.如:分号;可以代替为&59,句点.可以代替为&46,以这样的形式我们再次提交代码.果然,上面整个的过滤机制几乎完全失效了.根本没起到什么防御作用.

看了以上的攻击方法,是不是觉得很郁闷?如果才能避免上面的问题发生?

1.如果你的站点程序含论坛，留言板,以及其他程序中含提交数据格式的,没有很好过滤机制,请马上下载升级程序或是停止使用.避免造成更多的问题.

2.各提供免费论坛,免费申请留言板的站,请将UBB格式关闭.或找到更好的解析UBB.ASP程序页.

3.对一些会编写ASP的朋友,我建议大家过滤一下字符:(全部字符将写在[]中)如:

[“|.|;|:|\|/|&|$|#|`|)|,|'|”|-|~|[|(||] 注:其中|为分割符

4.对于某些站点建议去掉问题比较多的留言板,或是其他含提交的程序.如:原www.sangel.net使用的guestbook,一些不知名的程序体问题更是多多.建议换掉它.

5.我本人也修改了一个国外的留言板,前台基本已经修改完毕.后台这几天正在赶.修改后感觉在文本提交和输入以及留言时都有了很好的过滤.本来打算全采用英文界面,但考虑到N多chinese 对英语不过关,我还是将部分程序汉化了.有兴趣的朋友可以www.e3i5.com/guestbook/ 测试.我们欢迎善意的测试.

以下是国内一些经过本人测试的站点的留言板有问题的地址,(请各位站长注意啦,及时修补程序免得造成不必要的麻烦),测试的时间比较短一些，可能有一些站点没有找到.可能存在着更严重的问题.

以上就是这次脚本攻击测试的全部内容，

UBB的跨站脚本攻击的漏洞服务器教程

，

综观全部站点，看上去安全性真是的不敢再想.问题多多.

虽然只是举手之劳就可以解决的问题,但稍不小心就会造成大问题。

篇4：执行带嵌入参数的sql――spexecutesql数据库教程

执行

通常执行sql语句，大家用的都是exec，exec功能强大，但不支持嵌入参数，sp_executesql解决了这个问题，

执行带嵌入参数的sql――spexecutesql数据库教程

。抄一段sqlserver帮助：

sp_executesql

执行可以多次重用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。

语法

sp_executesql [@stmt =] stmt

[

{, [@params =] N'@parameter_name data_type [,...n]' }

{, [@param1 =] 'value1' [,...n] }

]

参数

[@stmt =] stmt

包含 Transact-SQL 语句或批处理的 Unicode 字符串，stmt 必须是可以隐式转换为 ntext 的 Unicode 常量或变量。不允许使用更复杂的 Unicode 表达式（例如使用 + 运算符串联两个字符串）。不允许使用字符常量。如果指定常量，则必须使用 N 作为前缀。例如，Unicode 常量 N'sp_who' 是有效的，但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。

stmt 可以包含与变量名形式相同的参数，例如：

N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'

stmt 中包含的每个参数在 @params 参数定义列表和参数值列表中均必须有对应项。

[@params =] N'@parameter_name data_type [,...n]'

字符串，其中包含已嵌入到 stmt 中的所有参数的定义。该字符串必须是可以隐式转换为 ntext 的 Unicode 常量或变量。每个参数定义均由参数名和数据类型组成，

n 是表明附加参数定义的占位符。stmt 中指定的每个参数都必须在 @params 中定义。如果 stmt 中的 Transact-SQL 语句或批处理不包含参数，则不需要 @params。该参数的默认值为 NULL。

[@param1 =] 'value1'

参数字符串中定义的第一个参数的值。该值可以是常量或变量。必须为 stmt 中包含的每个参数提供参数值。如果 stmt 中包含的 Transact-SQL 语句或批处理没有参数，则不需要值。

附加参数的值的占位符。这些值只能是常量或变量，而不能是更复杂的表达式，例如函数或使用运算符生成的表达式。

返回代码值

0（成功）或 1（失败）

结果集

从生成 SQL 字符串的所有 SQL 语句返回结果集。

例子（感谢邹建提供）

declare @user varchar(1000)

declare @moTable varchar(20)

select @moTable = 'MT_10'

declare @sql nvarchar(4000) --定义变量,注意类型

set @sql='select @user = count(distinct userid) from '+@moTable --为变量赋值

--执行@sql中的语句

exec sp_executesql @sql

,N'@user varchar(1000) out' --表示@sql中的语句包含了一个输出参数

,@user out --和调用存储过程差不多,指定输出参数值

print @user

本例中，@moTable 为嵌入参数。

篇5：通过一条sql语句访问不同数据库服务器中的数据库对象的方法数据库教程

对象|访问|服务器|数据|数据库|语句

在我们做数据库程序开发的时候，经常会遇到这种情况：需要将一个数据库服务器中的数据导入到另一个数据库服务器的表中，通常我们会使用这种方法：先把一个数据库中的数据取出来放到某出，然后再把这些数据一条条插入到目的数据库中，这种方法效率较低，写起程序来也很繁琐，容易出错。另外一种方法是使用bcp或BULK INSERT语句，将数据导入到一个文件中，再从此文件中导出到目的数据库，这种方法虽然效率稍高，但也有很多不如意的地方，单是在导入时怎样找到另外一台机器上的数据导入文件就很麻烦。

最方便的一种方法，我想也是效率最高的方法，应该是这样：

比如有两个数据库服务器：zl和ljw，里面都有一个数据库taxitemp（也可以不同名），数据库里有一个表，叫users，我们现在想把zl中的users数据导入到ljw中，可以这样写sql语句（假设现在连接的是zl数据库）：

insert into ljw.taxitemp.dbo.users

select * from users

这样，通过一条sql语句就完成了不同数据库服务器之间的数据复制，

有人会说，这种sql语句我也会写，我也想到了，但是没办法执行。

的确，单纯的这样一条语句没办法执行，因为数据库不知道ljw是什么服务器，也不知道怎样登录，当然会报错。

我们可以这样解决注册远程数据库服务器和登录的问题：

注册远程数据库服务器：

EXEC sp_addlinkedserver 'ljw', N'SQL Server'

注册远程数据库服务器的登录方法：

EXEC sp_addlinkedsrvlogin 'ljw', 'false', NULL, 'sa', 'zz'