Come evitare iniezioni SQL lato DB in SQLServer, Oracle, MySQL

 Come evitare iniezione SQL: lato SQL Server (SP_EXECUTESQL)

In generale va evitato nel modo più assoluto la scrittura di query sql diamiche.

Va evitato cioè  l’uso lato SQL Server di sp_executesql e EXEC

Di seguito mostriamo come trasformare una query “dinamica” in una “statica”

 

CREATE PROCEDURE search_orders @custid   nchar(5)     = NULL,

@shipname nvarchar(40) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +

' FROM dbo.Orders WHERE 1 = 1 '

IF @custid IS NOT NULL

SELECT @sql = @sql + ' AND CustomerID LIKE ''' + @custid + ''''

IF @shipname IS NOT NULL

SELECT @sql = @sql + ' AND ShipName LIKE ''' + @shipname + ''''

EXEC(@sql)

….

In tale esempio, a fronte di un form con parametri opzionali come custid e shipname, viene erroneamente utilizzata una query dinamica. Il modo corretto di procedere è invece il seguente.

1 Definire la stored procedure come:

 

CREATE PROCEDURE search_orders @custid   nchar(5)     = NULL,

@shipname nvarchar(40) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT OrderID, OrderDate, CustomerID, ShipName
FROM dbo.Orders
WHERE ((@custid  IS NULL) OR (CustomerID like @custid))
AND   ((@shipname IS NULL) OR (ShipName LIKE @shipname))

….

 

2 Far sì che la stored procedure  va chiamata attraverso l’uso dei Parameters descritti nel paragrafo precedente.

 

La query così definita ha i seguenti vantaggi:

  1. E’ molto sicura
  2. E’ facile da mantenere (per esempio per aggiungere/togliere condizioni)
  3. E’ compilata come stored procedure, e quindi il suo piano di esecuzione viene ottimizzato dopo ogni esecuzione (una query passata ad EXEC è interpretata, compilata, ecc ogni volta e molte versioni di SQL Server di norma  non mantengono il piano di esecuzione di una query on the fly)
  4. Poiché le condizioni in OR sono facili da verificare, il database ottimizza automaticamente la query a fronte degli input, evitando le verifiche inutili. Per esempio se sono nulli entrambi gli input, il database effettua una semplice query
    SELECT OrderID, OrderDate, CustomerID, ShipName FROM dbo.Orders
  5. Non dipende da estensioni proprietarie e quindi è più facile da migrare a differenti versioni di SQL Server o di Oracle.
  6. E’ facile da leggere, capire e trascrivere.

 

Per maggiori informazioni

SQL Server Backup & Restore

E’ facile fare un backup con sql server: Basta selezionare tasto destro Tasks>>Backup su un db. Ma come fare il restore?…
Ecco un semplice script che chiarisce la cosa (non sempre lampante dallo wizard di restore….):

-- Usare il seguente comando per recuperare i parametri sorgente da usare nella MOVE
-- Nel nostro caso ssaranno MY_BACKUP e MY_BACKUP_log
RESTORE FILELISTONLY 
 FROM DISK = N'C:\TEMP\MY_BACKUP\Backup.bak'  ;

--Ora usiamo questi parametri...
RESTORE DATABASE MY_BACKUP_Backup 
   FROM DISK = N'C:\TEMP\MY_BACKUP\Backup.bak'  
   WITH MOVE 'MY_BACKUP' TO 'C:\MY_DB_FILES\MY_BACKUP_Backup.mdf',
   MOVE 'MY_BACKUP_log' TO 'C:\MY_DB_FILES\MY_BACKUP_Backup.ldf', STATS = 10;
GO

SQL Server e Python

L’integrazione tra .NET e SQL Server è data per scontata: in questo articolo esploreremo invece  come accedere a SQL Server da Python. Uno dei vantaggi di python è che è un linguaggio molto facile da imparare, con un notevole numero di librerie e un’ottima integrazione multi piattaforma. Oltre a ciò python offre un sistema di installazione rapido delle librerie (come  Ruby e Perl).

Pyodbc: introduzione

Vi sono parecchie librerie odbc/SQLServer per python. Abbiamo deciso di presentare pyodbc.
Da Windows, potete installare pyodbc nel seguente modo:

$easy_install pyodbc
Searching for pyodbc
Reading http://pypi.python.org/simple/pyodbc/
Reading http://code.google.com/p/pyodbc
Reading http://code.google.com/p/pyodbc/downloads/list
Best match: pyodbc 2.1.8
Downloading http://pyodbc.googlecode.com/files/pyodbc-2.1.8.win32-py2.6.exe
Processing pyodbc-2.1.8.win32-py2.6.exe
creating 'c:\docume~1\giorgig\impost~1\temp\easy_install-53xdpl\pyodbc-2.1.8-py2.6-win32.egg' and adding 'c:\docume~1\giorgig\impost~1\temp\
easy_install-53xdpl\pyodbc-2.1.8-py2.6-win32.egg.tmp' to it
Moving pyodbc-2.1.8-py2.6-win32.egg to c:\python26\lib\site-packages
Adding pyodbc 2.1.8 to easy-install.pth file

Installed c:\python26\lib\site-packages\pyodbc-2.1.8-py2.6-win32.egg
Processing dependencies for pyodbc
Finished processing dependencies for pyodbc

Poi seguendo un semplice esempio, ecco come scoprire la somma di due numeri…

cs=r'DRIVER={SQL Server};SERVER=mypc\SQLEXPRESS;DATABASE=test1;UID=test1;PWD=test1';
cnxn = pyodbc.connect(cs);
cursor = cnxn.cursor();
cursor.execute("SELECT 1+2");
print(str(cursor.fetchone()));

Al momento non sembra esistere un modo semplice per connettersi alla versione embedded (compact) di SQL. Risulta molto comoda però l’integrazione con Excel, con sintassi del tipo:

pyodbc.connect("Driver={Microsoft Excel Driver (*.xls)}; Dbq=Sample.xls", autocommit=True)

Conclusioni e letture ulteriori

Con la seguente libreria è possibile integrare python django e sql server.

SQL Server: meta programmazione e meta descrittori

In questo articolo della serie SQL Server, vedremo come creare  in modo dinamico delle istruzione per impostare delle policy di sicurezza. Creeremo da zero una stored procedure chiamata sp_FixOverallGrant che in modo dinamico imposterà i profili di sicurezza per due utenze, una in lettura e scrittura (EBRB0_APP) ed una in sola lettura (EBRB0_USR).

In particolare vedremo come sia possibile sia eseguire SQL dinamico che accedere alla struttura interna del database.
Dal punto di vista idiomatico SQL Server utilizza il seguente codice per cancellare un elemento, per esempio una stored procedure:

/****** Object:  StoredProcedure [dbo].[sp_FixOverallGrant]    Script Date: 01/24/2011 14:51:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FixOverallGrant]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_FixOverallGrant]
GO

La linea 2 effettua una select nella meta tabella sys.objects, cercando se esiste una stored procedure chiamata sp_FixOverallGrant.

Vediamo ora il codice completo della stored procedure:


CREATE PROCEDURE dbo.sp_FixOverallGrant
AS
 BEGIN
	DECLARE @mycur CURSOR
	DECLARE @cmdx NVARCHAR(200)
	PRINT 'sp_FixOverallGrant Inizio fixes...';
	PRINT 'Fase 1 di 2: Fix grant su procedure e funzioni';
	-- Fix Stored procedure
	SET @mycur = CURSOR
	FOR
		-- Stored Procedure e FUNCTIONS
		SELECT 'GRANT EXECUTE ON [dbo].[' +name +'] TO [EBRB0_APP];'
		FROM sys.objects WHERE 
		(type in (N'P', N'PC')  OR (type in (N'FN', N'IF', N'TF', N'FS', N'FT')))
		and name LIKE 'sp%' and name not in 
		( 'sp_creatediagram','sp_helpdiagramdefinition', 'sp_renamediagram', 'sp_alterdiagram', 'sp_dropdiagram',
		  'sp_upgraddiagrams', 'sp_helpdiagrams' )
		ORDER BY name;
	OPEN @mycur

	-- Exec first
	FETCH NEXT FROM @mycur INTO @cmdx;

	WHILE @@FETCH_STATUS = 0
	BEGIN	
		PRINT 'Eseguo '+@cmdx;
		EXEC dbo.sp_executesql @statement = @cmdx;
		FETCH NEXT FROM @mycur INTO @cmdx
	END

	CLOSE @mycur
	DEALLOCATE @mycur




	-- Tabelle: Grant per le due utenze EBRB0_APP e EBRB0_USR
	-- 
	DECLARE @tabcur CURSOR	

	PRINT 'Fase 2 di 2: Fix grant su tabelle';
	-- Fix Stored procedure
	SET @tabcur = CURSOR
	FOR
		-- Tabella
		SELECT 'GRANT INSERT,SELECT,UPDATE,DELETE ON [dbo].['+name+'] TO [EBRB0_APP]; GRANT SELECT ON [dbo].['+name+'] TO [EBRB0_USR]; ' 
		FROM sys.objects WHERE type in (N'U') and name LIKE 'T_E%' ORDER BY NAME;
	OPEN @tabcur

	-- Exec first
	FETCH NEXT FROM @tabcur INTO @cmdx;

	WHILE @@FETCH_STATUS = 0
	BEGIN	
		PRINT 'Eseguo '+@cmdx;
		EXEC dbo.sp_executesql @statement = @cmdx;
		FETCH NEXT FROM @tabcur INTO @cmdx
	END

	CLOSE @tabcur
	DEALLOCATE @tabcur
	PRINT 'sp_FixOverallGrant terminata';
END
GO

EXEC sp_FixOverallGrant;

Le linee 9-29 servono per effettuare una query sui meta dati e creare delle istruzioni SQL per le grant in esame.
La linea 27 mostra come eseguire una stringa di comando sql in modo dinamico.
Il codice si ripete dalla linea 34 per la gestione delle tabelle.
Infine l’ultima istruzione esegue la stored procedure.

Introduzione a SQL Server 2008 Express

This entry is part 1 of 1 in the series SQL Server

In questo articolo parleremo di Microsoft SQL Server.

SQL Server nasce inizialmente da uno sforzo congiunto di Microsoft, Sybase and Ashton-Tate che nel 1989 creano tale prodotto avente come target OS/2.

Dopo alcune vicessitudini Microsoft si smarca da Sybase e SQL Server versione 6.0 è la prima versione per Windows NT che non ha più dipendenze di copyright da Sybase.

Continue reading “Introduzione a SQL Server 2008 Express”