Database/MS-SQL +31

Loading..DB 내의 특정 구문 일괄 삭제
2016.07.25

뷰어로 보기
Loading..MS-SQL 모든 사용자 연결 끊기
2016.07.25

뷰어로 보기
Loading..php + MS-SQL 연동시 확인 사항
2016.07.22

뷰어로 보기

DB 내의 특정 구문 일괄 삭제

Database/MS-SQL2016. 7. 25. 13:58

뷰어
댓글로
이전글
다음글

SQL Injection 또는 외부 침입으로 인하여 DB 에 비정상 자료 등록되는 경우가 발생함

스팸게시글 등록 또는 광고 웹페이지 경로 등록 등 게시글 등록시 일괄 삭제 필요함

순서 1

프로시저 생성

CREATE PROCEDURE [DB_DEL_TEXT]
AS

DECLARE @TP int;
DECLARE @TBL varchar(255), @CLN varchar(255), @DEL_STR varchar(255);
SET @DEL_STR='광고광고광고<script src=http://fuckingchina.com></script>'   -- 여기의 ' ' 안의 내용이 삭제 될 예정인 내용

DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name ,b.xtype
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167 OR b.xtype=175 OR b.xtype=239 );

OPEN Table_Cursor;

FETCH NEXT FROM Table_Cursor INTO @TBL, @CLN, @TP;
WHILE (@@FETCH_STATUS = 0) BEGIN
    IF @TP > 100
    BEGIN
          EXEC('UPDATE ['+ @TBL +'] set [' + @CLN + '] = REPLACE('+ @CLN+' , '''+@DEL_STR +''','''' )');
    END
    ELSE
    BEGIN
                    EXEC('UPDATE ['+ @TBL +'] set [' + @CLN + '] = REPLACE(CONVERT(varchar(8000),'+ @CLN+') , '''+@DEL_STR +''','''' )');
    END
FETCH NEXT FROM Table_Cursor INTO @TBL, @CLN,@TP;
END;

CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
GO

순서2

일괄 삭제를 진행 할 DB 접근하여 새 쿼리 실행 후 생성 된 프로시저 실행

Exec DB_DEL_TEXT

저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

X 행에 데이터가 커밋되지 않았습니다. (0)	2016.07.25
DB 오프라인, 온라인 (0)	2016.07.25
MS-SQL 모든 사용자 연결 끊기 (0)	2016.07.25
php + MS-SQL 연동시 확인 사항 (0)	2016.07.22
SQL 2014 로 DB 이전 후 테이블 행 편집시 나오는 에러 문구 (0)	2016.07.21

MS-SQL 모든 사용자 연결 끊기

Database/MS-SQL2016. 7. 25. 13:51

뷰어
댓글로
이전글
다음글

ALTER DATABASE [사용자데이터베이스] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--활성화되어 있는 트랜잭션을 즉시 처리 싱글유저모드

ALTER DATABASE [사용자데이터베이스] SET SINGLE_USER WITH ROLLBACK AFTER 60;

--활성화 되어 있는 트랜잭션을 60초 후에 모두 처리하고 싱글유저모드 변경

ALTER DATABASE [사용자데이터베이스] SET MULTI_USER

--싱글 유저 모드를 다시 멀티 유저 모드로 변경 (이거 꼭 해야 함!!!)

저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

DB 오프라인, 온라인 (0)	2016.07.25
DB 내의 특정 구문 일괄 삭제 (0)	2016.07.25
php + MS-SQL 연동시 확인 사항 (0)	2016.07.22
SQL 2014 로 DB 이전 후 테이블 행 편집시 나오는 에러 문구 (0)	2016.07.21
DB 계정 암호 변경시 복잡성 규칙 피하기 (0)	2016.07.21

php + MS-SQL 연동시 확인 사항

Database/MS-SQL2016. 7. 22. 13:54

뷰어
댓글로
이전글
다음글

MS 기술문서 페이지에서 긁어옴

System Requirements (Microsoft Drivers for PHP for SQL Server)

To access data in a SQL Server 2005 or later database using the Microsoft Drivers for PHP for SQL Server (SQL Server 2008 or later if using version 3.2 or 3.1), you must have the following components installed on your computer:

Supported operating systems for version 3.2 and 3.1 of the driver include:
- Windows Server 2008 R2 SP1
- Windows Vista SP2
- Windows Server 2008 SP2
- Windows 7 SP1
- Windows Server 2012
- Windows Server 2012 R2
- Windows 8
- Windows 8.1
Supported operating systems for version 3.0 of the driver include:
- Windows Server 2008 R2 SP1
- Windows Vista SP2
- Windows Server 2008 SP2
- Windows 7 SP1
Supported operating systems for version 2.0 of the driver include:
- Supported operating systems for version 2.0 of the driver include:
- Windows Server 2003 Service Pack 1
- Windows XP Service Pack 3
- Windows Vista Service Pack 1 or later
- Windows Server 2008
- Windows Server 2008 R2
- Windows 7
SQL Azure Databases are supported. For information see Connecting to Windows Azure SQL Database.
PHP 5.x is required. For information about how to download and install the latest stable binaries, seehttp://php.net.
Microsoft Drivers for PHP for SQL Server, versions require PHP versions in the following table:

Microsoft Drivers for PHP for SQL Server Version	Supported PHP Versions
3.2	PHP 5.6.4+ or PHP 5.5.16+ or PHP 5.4.32
3.1	PHP 5.5.16+ or PHP 5.4.32
3.0	PHP 5.4.32 or PHP 5.3.0
2.0	PHP 5.3.0 or PHP 5.2.4 or PHP 5.2.13

A version of the driver file must be in your PHP extension directory. See Driver Versions later in this topic for information about the different driver files. See Loading the Microsoft Drivers for PHP for SQL Server for information on configuring the driver for the PHP runtime. To download the drivers, see Microsoft Drivers for PHP for SQL Server.
A Web server is required. Your Web server must be configured to run PHP. For information about hosting PHP applications with Internet Information Services (IIS) 6.0, see Using FastCGI to Host PHP Applications on IIS 6.0. For information about hosting PHP applications with IIS 7.0, see Using FastCGI to Host PHP Applications on IIS 7.0.

The Microsoft Drivers for PHP for SQL Server has been tested using IIS 6 and IIS 7 with FastCGI.

Note

Microsoft provides support only for IIS.
Versions 3.2 and 3.1 require Microsoft ODBC Driver 11 (or higher) for SQL Server. To download the Microsoft ODBC Driver 11 for PHP for SQL Server, see Microsoft ODBC Driver 11 for SQL Server.

If you are using the SQLSRV driver, sqlsrv_client_info will return information about which version of SQL Server Native Client is being used by the Microsoft Drivers for PHP for SQL Server. If you are using the PDO_SQLSRV driver, you can use PDO::getAttribute to discover the version.
For versions 3.0 and 2.0, the x86 version of SQL Server Native Client is required on the computer where PHP is running. If you are using a 64-bit operating system, the x86 version of SQL Server Native Client will be installed with the x64 version of SQL Server Native Client (do not install the x86 version of SQL Server Native Client on an x64 operating system).

If you are using the SQLSRV driver, sqlsrv_client_info will return information about which version of SQL Server Native Client is being used by the Microsoft Drivers for PHP for SQL Server. If you are using the PDO_SQLSRV driver, you can use PDO::getAttribute to discover the version.
- Version 3.0 of the driver requires Microsoft SQL Server 2012 Native Client. You can download Microsoft SQL Server 2012 Native Client from the SQL Server 2012 feature pack page.
- Version 2.0 of the driver requires Microsoft SQL Server 2008 R2 Native Client. Click on the appropriate link below:
  
  Download the X86 package
  
  Download the X64 package

Note
Microsoft provides support only for IIS.

Driver Versions

This section lists the drivers that are included with versions 3.2, 3.1, 3.0, and 2.0 of the Microsoft Drivers for PHP for SQL Server.

Follow the installation instructions in Loading the Driver to configure the driver for use with the PHP runtime.

Microsoft Drivers 3.2 for PHP for SQL Server installs the following versions of the driver:

Driver file	PHP version	Thread safe?	Use with PHP .dll
php_sqlsrv_54_nts.dll php_pdo_sqlsrv_54_nts.dll	5.4	no	php5.dll
php_sqlsrv_54_ts.dll php_pdo_sqlsrv_54_ts.dll	5.4	yes	php5ts.dll
php_sqlsrv_55_nts.dll php_pdo_sqlsrv_55_nts.dll	5.5	no	php5.dll
php_sqlsrv_55_ts.dll php_pdo_sqlsrv_55_ts.dll	5.5	yes	php5ts.dll
php_sqlsrv_56_nts.dll php_pdo_sqlsrv_56_nts.dll	5.6	no	php5.dll
php_sqlsrv_56_ts.dll php_pdo_sqlsrv_56_ts.dll	5.6	yes	php5ts.dll

Microsoft Drivers 3.1 for PHP for SQL Server installs the following versions of the driver:

Driver file	PHP version	Thread safe?	Use with PHP .dll
php_sqlsrv_54_nts.dll php_pdo_sqlsrv_54_nts.dll	5.4	no	php5.dll
php_sqlsrv_54_ts.dll php_pdo_sqlsrv_54_ts.dll	5.4	yes	php5ts.dll
php_sqlsrv_55_nts.dll php_pdo_sqlsrv_55_nts.dll	5.5	no	php5.dll
php_sqlsrv_55_ts.dll php_pdo_sqlsrv_55_ts.dll	5.5	yes	php5ts.dll

Microsoft Drivers 3.0 for PHP for SQL Server installs the following versions of the driver:

Driver file	PHP version	Thread safe?	Use with PHP .dll
php_sqlsrv_53_nts.dll php_pdo_sqlsrv_53_nts.dll	5.3	no	php5.dll
php_sqlsrv_53_ts.dll php_pdo_sqlsrv_53_ts.dll	5.3	yes	php5ts.dll
php_sqlsrv_54_nts.dll php_pdo_sqlsrv_54_nts.dll	5.4	no	php5.dll
php_sqlsrv_54_ts.dll php_pdo_sqlsrv_54_ts.dll	5.4	yes	php5ts.dll

Microsoft Drivers 2.0 for PHP for SQL Server installs the following versions of the driver:

Driver file	PHP version	Thread safe?	Use with PHP .dll
php_sqlsrv_53_nts_vc6.dll php_pdo_sqlsrv_53_nts_vc6.dll	5.3	no	php5.dll
php_sqlsrv_53_nts_vc9.dll php_pdo_sqlsrv_53_nts_vc9.dll	5.3	no	php5.dll
php_sqlsrv_53_ts_vc6.dll php_pdo_sqlsrv_53_ts_vc6.dll	5.3	yes	php5ts.dll
php_sqlsrv_53_ts_vc9.dll php_pdo_sqlsrv_53_ts_vc9.dll	5.3	yes	php5ts.dll
php_sqlsrv_52_nts_vc6.dll php_pdo_sqlsrv_52_nts_vc6.dll	5.2	no	php5.dll
php_sqlsrv_52_ts_vc6.dll php_pdo_sqlsrv_52_ts_vc6.dll	5.2	yes	php5ts.dll

If the name of the driver file contains "vc9", it should be used with a PHP version compiled with Visual C++ 9.0.

저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

DB 내의 특정 구문 일괄 삭제 (0)	2016.07.25
MS-SQL 모든 사용자 연결 끊기 (0)	2016.07.25
SQL 2014 로 DB 이전 후 테이블 행 편집시 나오는 에러 문구 (0)	2016.07.21
DB 계정 암호 변경시 복잡성 규칙 피하기 (0)	2016.07.21
테이블 디자인을 변경하려고 하니 "변경 내용을 저장할 수 없습니다" 메시지가 나왔다. (0)	2016.07.21

‹ Prev 1 2 3 4 5 6 7 ··· 11 Next ›

독까의 이야기

DB 내의 특정 구문 일괄 삭제

'Database > MS-SQL' 카테고리의 다른 글

MS-SQL 모든 사용자 연결 끊기

'Database > MS-SQL' 카테고리의 다른 글

php + MS-SQL 연동시 확인 사항

System Requirements (Microsoft Drivers for PHP for SQL Server)

Driver Versions

'Database > MS-SQL' 카테고리의 다른 글

최근에 달린 댓글

최근에 올라온 글

글 보관함

티스토리툴바

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31