Newsgroup: it.comp.software.database
Titolo: Domanda facile per SQL

Bisogna definire "a" come parametro di input...
CREATE PROCEDURE pippo
@a TINYINT
AS
.......................
dopo la chiami passando il parametro, per esempio
         pippo 1
oppure:
         pippo 2
Paulus
http://spazioweb.inwind.it/it_informatica
--------------------------------
Inviato via 
http://usenet.libero.it

 SELECT * FROM Prova WHERE + Clausola
Devi costruire una query dinamica, es:
declare @Clausola varchar(255)
declare @query varchar(255)
IF @a = 1
BEGIN
     select @Clausola = 'Pippo = 1 AND Pluto = 2'
 END
 ELSE
 BEGIN
    select  @Clausola = 'Pippo = a AND Pluto = b'
 END
select @query= 'Select * from prova where ' + @clausola
exec(@query)
Frilla

 SELECT * FROM Prova WHERE + Clausola
[SNIP]
Nettuno,
alcune considerazioni per quanto riguarda l'utilizzo
di comandi SQL costruiti dinamicamente.
La prima e' relativa a problemi di sicurezza, meglio
nota come "SQL Injection": una tecnica che permette
di eseguire comandi SQL arbitrari modificando la
natura della richiesta passando parametri opportuni.
Considera il seguente esempio:
-- CUT HERE --
-- Demo SQL Injection
USE tempdb
GO
IF OBJECT_ID('dbo.Utenti') IS NOT NULL
 DROP TABLE dbo.Utenti
GO
CREATE TABLE dbo.Utenti(
 IDUtente int IDENTITY(1, 1) NOT NULL,
 NomeUtente varchar(100)  NOT NULL,
 PassUtente varchar(100)  NOT NULL,
 CONSTRAINT pk_Utenti
 PRIMARY KEY (IDUtente)
)
GO
INSERT dbo.Utenti (NomeUtente, PassUtente)
VALUES ('Pippo', 'passwordpippo')
INSERT dbo.Utenti (NomeUtente, PassUtente)
VALUES ('Pluto', 'passwordpluto')
INSERT dbo.Utenti (NomeUtente, PassUtente)
VALUES ('Paperino', 'passwordpaperino')
INSERT dbo.Utenti (NomeUtente, PassUtente)
VALUES ('Paperoga', 'passwordpaperoga')
GO
IF OBJECT_ID('dbo.spsGetUtente') IS NOT NULL
 DROP PROCEDURE dbo.spsGetUtente
GO
CREATE PROCEDURE dbo.spsGetUtente
 @IDUtente int    = NULL,
 @NomeUtente varchar(100) = NULL,
 @PassUtente varchar(100) = NULL
AS
DECLARE @sqlstmt varchar(8000)
SET @sqlstmt = 'SELECT * FROM dbo.Utenti WHERE '
IF @IDUtente IS NOT NULL
 SET @sqlstmt = @sqlstmt + ' IDUtente = ' + CAST(@IDUtente as varchar)
ELSE
 SET @sqlstmt = @sqlstmt
  + ' NomeUtente = ''' + @NomeUtente + ''''
  + ' AND PassUtente = ''' + @PassUtente + ''''
PRINT 'Questo e'' il comando che viene eseguito:'
PRINT @sqlstmt
PRINT ''
EXEC (@sqlstmt)
GO
-- tutto bene
EXEC dbo.spsGetUtente 1
-- tutto bene
EXEC dbo.spsGetUtente NULL, 'pippo', 'passwordpippo'
-- ma cosa succede se passo ad esempio "' OR 1 = 1 --" come utente
-- ed una qualunque stringa come password ?
EXEC dbo.spsGetUtente NULL, ''' OR 1 = 1 --', 'blablabla'
-- viene ritornata l'intera tabella...
-- funziona anche altre stringhe...
-- peggio ancora se la sicurezza per l'utente non e' impostata
-- correttamente, ad esempio se si tratta di un web-server che
-- esegue le richieste come sysadmin...
EXEC dbo.spsGetUtente NULL, ''' OR 1 = 1 EXEC master.dbo.xp_cmdshell ''dir
c:\''--', 'blablabla'
-- ma basta anche solo un utente che sia db_owner nel database...
EXEC dbo.spsGetUtente NULL, ''' OR 1 = 1 DROP TABLE dbo.Utenti --',
'blablabla'
-- .. la tabella non esiste piu'....
SELECT * FROM dbo.Utenti
-- CUT HERE --
Tieni conto che questo e' un esempio un po' forzoso, sicuramente
le password non devono essere mantenute in chiaro nelle tabelle
ed una procedura che deve verificare le credenziali di un utente
dovrebbe verificare che il risultato della selezione ritorni solo
una riga che sia coerente con i dati in input.
E' una realta' pero' che moltissimi siti hanno sofferto o soffrono
tutt'ora di problemi di questo tipo (sempre meno ad essere sinceri,
ma qualche tempo fa' erano veramente moltissimi).
Solitamente per ovviare al problema si procede verificando che
non esistano caratteri tipo l'apice nei parametri in entrata
La seconda considerazione e' legata alle prestazioni.
L'esecuzione di un comando creato dinamicamente tramite
EXEC non da' nessuna garanzia che la versione compilata
del comando (il piano di esecuzione) permanga nella cache
dei piani di esecuzione, annullando di fatto i benefici
derivanti dall'utilizzo di Stored Procedures.
Per ovviare a questo problema e' possibile usare la stored
procedure di sistema sp_executesql come in questo exempio:
-- CUT HERE --
USE Northwind
GO
IF OBJECT_ID('dbo.spsGetCustomer') IS NOT NULL
 DROP PROCEDURE dbo.spsGetCustomer
GO
CREATE PROCEDURE dbo.spsGetCustomer
 @CustomerID  nchar(5)  = NULL,
 @ContactName nvarchar(30) = NULL
AS
IF @CustomerID IS NOT NULL
 EXEC sp_executesql
   N'SELECT * FROM dbo.Customers WHERE CustomerID = @1',
   N'@1 nchar(5)', @CustomerID
ELSE
 EXEC sp_executesql
   N'SELECT * FROM dbo.Customers WHERE ContactName = @1',
   N'@1 nvarchar(30)', @ContactName
GO
EXEC dbo.spsGetCustomer 'ALFKI'
EXEC dbo.spsGetCustomer NULL, 'Pedro Afonso'
-- CUT HERE --
Che forza l'inserimento del comando nella cache dei piani di
esecuzione, puoi verificarlo tu stesso eseguendo la seguente
query:
SELECT *
FROM master.dbo.syscacheobjects
WHERE sql LIKE '%dbo.Customers%'
Nota che statemente SQL molto semplici possono essere
automanticamente "auto-parametrati" ed entrare nella
cache come piani parametrici.
Se vuoi una dissertazione piu' esaustiva sui problemi legati
all'uso di SQL dinamico, ti consiglio il seguente articolo:
http://www.algonet.se/~sommar/dynamic_sql.html
Ciao,
--
Gianluca Hotz
Backoffice MVP (SQL Server) MCP SQL Server and MCP Windows
Vice-President of the Italian User Group for SQL Server
Founding Member of the Italian User Group for .NET
http://www.ghotz.com
http://www.ugiss.org