Category Archives: SQL

Scripts, SQL

Movimentar arquivos de Bancos de Dados

Leave a comment

Nesse post vou falar como movimentar os arquivos de um banco de dados de uma unidade/diretório para outra unidade/diretório.

Ai você pensa:

“Pra que? Já sei como faz isso. Não tem nenhuma novidade.” ou

“Aaahh,,, isso é facil, uso o bom e velho backup/restore ou o detach/attach…”

Isso é verdade, não tem nenhuma grande novidade em fazer a movimentação mas, ai vem a parte divertida, pra que fazer do método fácil se podemos fazer do método interessante?

1.- Backup/Restore

Se executarmos o bom e velho backup/restore, no momento do restore podemos alterar o caminho onde os arquivos irão ser gravados, isso é simples.

Apenas precisaremos executar o processo de backup que, dependendo do tamanho da base, pode demorar alguns minutos ou até umas horas. Além de ter que ter um espaço para armazenar o backup ou executá-lo através de uma unidade de backup.

2.- Detach/Attach

Usando o Detach ganhamos algum tempo, você vai apenas mover os arquivos de log e dados para outro lugar e anexar a base novamente.

O problema disso é se você usar o Service Broker ou até o Mirror, você terá que refazer o processo.

3.- Alter Database (o “método interessante”)

Esse “método” a seguir cria uma procedure para executar a movimentação do banco para outro local usando o Alter database, mas é possível executá-lo em diversos bancos como vou demonstrar:

Vamos criar 2 bases de dados para testar:

create database banco1

create database banco2

Agora execute o scritp abaixo para criar a procedure que irá executar a movimentação dos arquivos:

IF ( OBJECT_ID(‘dbo.sp_MoveDatabase’) IS NOT NULL )
begin
DROP PROCEDURE dbo.sp_MoveDatabase
end
GO

create procedure sp_MoveDatabase

@NewDataFolder nvarchar(1000),
@NewLogFolder nvarchar(1000),
@DbList nvarchar(4000)

as
Begin
declare @DbTable table (lkey int identity (1,1) primary key, dbname nvarchar(100))
declare @FileTable table (lkey int identity (1,1) primary key, [name]nvarchar(100), physical_name nvarchar(1000), [type] int )
declare @sql nvarchar(4000)
declare @count int, @RowNum int
declare @DbName nvarchar(100)
declare @OldPath nvarchar(1000)
declare @Type int
declare @LogicalName nvarchar(100)
declare @ParmDefinition nvarchar(1000)
declare @FileName nvarchar(100)
declare @NewPath nvarchar(1000)
declare @ShowAdvOpt int
declare @XPCMD int

set nocount on;

if right(@DbList,1) = ‘,’

Begin
print ‘DbList must NOT end with “””‘
return

End
declare @MyString NVARCHAR(100)
declare @Pos INT
declare @NextPos INT
declare @String NVARCHAR(4000)
declare @Delimiter NVARCHAR(1)

set @String = @DbList
set @Delimiter = ‘,’
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)
WHILE (@pos <> 0)

BEGIN
SET @MyString = substring(@String,1,@Pos – 1)
insert into @DbTable (dbname) values (LTRIM(RTRIM(@MyString)))
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)

END
set @ShowAdvOpt = cast(( select [value] from sys.configurations where [name] = ‘show advanced options’) as int)
set @XPCMD = cast(( select [value] from sys.configurations where [name] = ‘xp_cmdshell’) as int)
if right(@NewDataFolder,1)<> ‘\’ or right(@NewLogFolder,1)<>’\’

Begin
print ‘new path”s must end with \’
return
end
EXEC sp_configure ‘show advanced option’, ‘1’
RECONFIGURE

exec sp_configure ‘xp_cmdshell’ , ‘1’
RECONFIGURE

print ‘NewMdfFolder is ‘ + @NewDataFolder
print ‘NewLdfFolder is ‘ + @NewLogFolder

SET @RowNum = 1
SET @count = (select count(*) from @DbTable)
while @RowNum <= @count

Begin
select @DbName = DBName from @DbTable
where lKey = @RowNum
set @sql = ‘select name, physical_name, type from ‘ + @DbName + ‘.sys.database_files’
insert into @FileTable
exec sp_executesql @sql

— Derruba todas as conexoes configurando como single user with immediate
set @sql= ‘ALTER DATABASE [‘ + @DbName + ‘] SET SINGLE_USER WITH ROLLBACK IMMEDIATE’
print ”
print ‘Executando linha -‘ + @sql
exec sp_executesql @sql

— configura db off line
set @sql = ‘ALTER DATABASE [‘ + @DbName + ‘] SET OFFLINE;’
print ”
print ‘Executando linha – ‘ + @sql
exec sp_executesql @sql
select * from @FileTable
while @@rowcount > 0

begin
select top 1 @OldPath = physical_name, @Type = [type], @LogicalName = [name] from @FileTable

–move arquivos files
set @FileName = (SELECT REVERSE(SUBSTRING(REVERSE(@OldPath), 0, CHARINDEX(‘\’, REVERSE(@OldPath), 1))))
if @type = 0

begin
set @NewPath = @NewDataFolder + @FileName
end
else
begin

set @NewPath = @NewLogFolder + @FileName
end
set @Sql = ‘EXEC master..xp_cmdshell ”MOVE “‘ + @OldPath + ‘” “‘ + @NewPath +'””’
print ”
print ‘Executando linha -‘ + @sql
exec sp_executesql @sql

— altera caminho dos arquivos
set @sql = ‘ALTER DATABASE ‘ + @DbName + ‘ MODIFY FILE (NAME = ‘ + @LogicalName + ‘, FILENAME = “‘ + @NewPath + ‘”)’
exec sp_executesql @sql
delete from @FileTable where [name] = @LogicalName
select * from @FileTable
end –while

set @sql = ‘ALTER DATABASE [‘ + @DbName + ‘] SET ONLINE;’
print ”
print ‘Executing line -‘ + @sql
exec sp_executesql @sql
SET @RowNum = @RowNum + 1

— aceita multi user novamente.
set @sql= ‘ALTER DATABASE [‘ + @DbName + ‘] SET MULTI_USER’
print ”
print ‘Executing line -‘ + @sql
exec sp_executesql @sql
end
exec sp_configure ‘xp_cmdshell’ , @XPCMD

reconfigure
EXEC sp_configure ‘show advanced option’, @ShowAdvOpt

RECONFIGURE
End –procedure

** ATENÇÃO **

Os diretórios de destino já devem estar criados, caso você execute a procedure sem criar o destino o script vai alterar o caminho da base e não vai movimentar os arquivos e quando ela foi trazida online ela vai apresentar erro.

** ATENÇÃO 2 **

Esse script é do tipo “Vai filhão”, quando ele for executado todos os usuários conectados serão desconectados sem dó nem piedade, todas as transações que eles estiverem executando serão dropadas. As conexões apenas poderão ser reestabelecidas após o termino da movimentação.

Para executar a procedure execute-a da seguinte forma:

exec sp_MoveDatabase @NewDataFolder = ‘c:\teste\’, @NewLogFolder = ‘d:\teste\’, @sDbList = ‘banco1, banco2’

Se tudo der certo, as bases banco1 e banco2 estarão seus novos destinos.

SQL

Alterar língua do FullText

Leave a comment

A algum tempo atrás tive que alterar a língua de pesquisa do FullText do SQL 2005 de Neutral para Portugês.

Em teoria um procedimento bem simples, acha o FullText, edita dele e troca na caixa pra Português.

Mas, como sempre, nada é tão simples assim…

Descobri que por padrão o SQL 2005 não traz alguns idiomas, para adicionar o Português tive que fazer o procedimento do KB908441.

Mas, ao invés de ficar lendo o KB vo descrever o passo a passo pra fazer funcionar:

IMPORTANTE !!!

Se você utilizar mais de uma instância do SQL no mesmo servidor você vai ter que fazer o procedimento mais de uma vez.

Ok, sabendo disso, agora vamos começar.

Levando em conta que provavelmente você está usando uma instância, ela é conhecida como MSSQL.1

1. Clique em Start, clique em Run, digite regedit e clique em OK

2. Dentro do Editor de Registro, localize a seguinte chave:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\CLSID

3. No menu Edit escolha New e clique em Key

4. Digite {25B7FD48-5404-4BEB-9D80-B6982AF404FD} e pressione Enter

5. No painel da direita, modifique o valor Default

6. Em Edit String, digite ptblr.dll e clique em OK

7. No menu Edit escolha New e clique em Key

8. Digite {D5FCDD7E-DBFF-473F-BCCD-3AFD1890EA85} e pressione Enter

9. No painel da Direita, modifique o valor Default

10. Em Edit String digite ptblr.dll e clique em OK

Adicione uma sub-chave ao MSSQL.1\MSSearch\Language

11. No menu Edit escolha New e clique em Key

12. Digite ptb e pressione Enter

13. Na Tabela abaixo existem 4 itens que devem ser criados:

Tipo Nome Valor
String NoiseFile C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseptb.txt
String TsaurusFile C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\tsptb.xml
DWord Locate 00000416
String WBreakerClass {25B7FD48-5404-4BEB-9D80-B6982AF404FD}
String SemmerClass {D5FCDD7E-DBFF-473F-BCCD-3AFD1890EA85}

Depois desse pequeno processo, pelo menos no meu caso, não tive que reinicia o SQL para aparecer Portugês, mas tive que executar um Rebuild do FullText.

SQL

SQL Injection

Leave a comment

Problema:
Esse tipo de ataque tem um número grande de coincidencias que apontão para a mesma fonte.
Com essa informação em mãos, a resolução deveria ser simples.
Com o seu site atacado por SQL-Injection como você pode identificar, analizar, recuperar e resolver o problema? E o que você pode aprender com isso?

Solução:
Como ponto de referencia, SQL-Injection é um exploit onde comandos SQL são passados para o servidor de SQL de uma forma maliciosa.
Analisando a situação, podemos chegar em 4 componentes básicos:
1º Identificação do Problema;
2º Análise da situação para determinar a causa raiz tão como opções de saída para recuperação e solução, que é seguido por recuperar os dados e prevenir por um problema futuro.
3º Plano de comunicação
4º Como resolver o incidente caso ocorra novamente.

Vamos por partes…

Identificar:

Usualmente os usuários ou clientes reportão o caso para o time de TI.
Com o problema detectado a análise precisa ser conduzida nesses passos:
– Logs do Firewall
– logs do IIS
– Páginas Web
– Tabelas do SQL Server

Em termos de exemplificação de código, strings são passadas para o SQL Server. Após ver o código, um simples cursor para atualizar colunas varchar, nvarchar, text ou ntext pode ser inserido através da URL ou através da própria página.

Algumas referencias:

* Http://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx#attack-description
* http://www.theregister.co.uk/2008/08/07/new_sql_attacks/
* http://isc.sans.org/diary.html?storyid=4840

Analise

Após rever os Logs do IIS é possível traduzir o código, mas se você tiver uma ferramenta de Performance Monitor, ela pode capturar longos tempos de execução de SQL que podem prover grande ajuda para identificar apartir de qual página foi feita a invasão.
Se você não possuir, terá que gastar um bom tempo para identificar por onde veio a invasão.
Uma vez que a string foi determinada esses 2 scripts podem lhe oferecer um grande auxilio para identificar as colunas que foram alteradas e/ou recuperar:

sp_LocalizaTexto

sp_TrocaTexto

Um método simples para identificar se seu site foi infectado é usando o google para rodar a seguinte query:
* Esse método foi passado pela SANs (http://isc.sans.org/diary.html?storyid=4840)

site:seu_site “script scr=http://*/””ngg.js”|”js.js”|”b.js”

Recuperando:

Existem algumas formas de recuperar os dados alterados
– Backup\Restore
Pro: Se você possui um método de recuperar os dados rapidamente e você sabe exatamente quando os dados foram infectados, pode ser uma forma rápida e simples para recuperar a integridade do site.
Con: Se você não sabe quando os dados foram alterados, você não saberá se o backup foi feito com as informações alteradas ou a janela de perda de dados poderá ser grande.

– Através de análise
Pro: Com o script “sp_TrocaTexto para SQL 2005, a informação maliciosa pode ser identificada e corrigida com confidencialidade.
Con: É interessante efetuar um backup da base de dados antes de executar as alterações para garantir integridade de informações e posterior análise.

Em ambas opções, o problema com o SQL-Injection já deverá ter sido resolvidor.

Resolução:
Possíveis passos para ajudar na solução:
– Desenvolvimento\DBA
– Validar os comandos SQL passados pelo front-end (IIS)
– Validar os dados e os tipos de dados passados
– Converter SQL dinâmico em Stored Procedures com parâmetros
– Remover páginas ou diretórios antigos que pode ser usados como pontos de partida para a invasão
– Prevenir que qualquer comando seja executado em conjunto com os seguintes comandos: ponto e virgura. EXEC, CAST, SET, dois pontos, apostrofe, etc..
– Baseado na linguagem de programação do seu front-end, determinar quais os caracteres especiais podem ser removidos antes de qualquer comando passado para o SQL Server

– Administradores de Rede
– Prevenir trafego de alguns IP´s em particular ou domínios
– Verificar se nas configurações de firewall existe algum item para prevenir quanto a SQL-Injection
– Procurar por produtos ou serviços para analisar seu código e site em busca de erros e prevenção
* http://www.acunetix.com/vulnerability-scanner/
* http://www.rapid7.com/nexpose/web-application-va.jsp
* http://www.fortify.com/products/detect/
* http://www.mcafeesecure.com/us/
* http://www.onlinehackscan.com/default.asp

Plano de comunicação:
O plano de comunicação deve ser atual, claro e importante.
Mantenha isso em mente.
Para muitas pessoas, não saber o que está acontecendo gera desconforto, anciedade, e achar que a equipe de TI não está fazendo seu trabalho.
Comunique sobre a descoberta do ocorrido, informe que o time está trabalhando para determinar a causa e solução do problema e que uma atualização desse status será repassada em até 4 horas;
Comunique o ocorrido, os passos tomados pela equipe de TI para resolver o problema e que a solução estará implantada em até 30 minutos;
e por final comunique que o incidente foi sanado, os passos para resolver o incidente e os passos futuros para prevenir novos problemas.

Recuperação Rápida:

Uma forma de recuperação rápida seria:
– Parar os serviços de Web
– Rever os logs do IIS para determinar os comandos utilizados para lozalizar a vulnerabilidade
– Converter o código para determinar quais tabelas foram alteradas
– Localizar e alterar as strings nas tabelas
– Corrigir as páginas/comandos que possuiam as vulnerabilidades
– Testar para identificar se não existem novos problemas
– Atualizar as páginas/ comandos nos servidores de produção
– Iniciar os serviços de web

Scripts

Como ver a versão do SQL

Leave a comment

Muitos já sabem que se utilizar o:

select @@version

terá as informações sobre a versão do SQL onde você está executando este select.
Mas se você executar:

SELECT
CAST( SERVERPROPERTY( ‘MachineName’ ) AS varchar( 30 ) ) AS MachineName ,
CAST( SERVERPROPERTY( ‘InstanceName’ ) AS varchar( 30 ) ) AS Instance ,
CAST( SERVERPROPERTY( ‘ProductVersion’ ) AS varchar( 30 ) ) AS ProductVersion ,
CAST( SERVERPROPERTY( ‘ProductLevel’ ) AS varchar( 30 ) ) AS ProductLevel ,
CAST( SERVERPROPERTY( ‘Edition’ ) AS varchar( 30 ) ) AS Edition ,
( CASE SERVERPROPERTY( ‘EngineEdition’)
WHEN 1 THEN ‘Personal or Desktop’
WHEN 2 THEN ‘Standard’
WHEN 3 THEN ‘Enterprise’
END ) AS EngineType ,
CAST( SERVERPROPERTY( ‘LicenseType’ ) AS varchar( 30 ) ) AS LicenseType ,
SERVERPROPERTY( ‘NumLicenses’ ) AS #Licenses;SELECT @@VERSION AS [SQL Server Details];