J'utilise une requête comme celle-ci:
ALTER procedure [dbo].[procedure]
@Search nvarchar(100) = ''
declare @LargeComplexQuery nvarchar(max) =
'select * from People where Name like ''%' + @Search + '% order by Name'
exec sys.sp_executesql @LargeComplexQuery
@Search est rempli par un programme. Si le programme renvoie une chaîne contenant un guillemet simple les erreurs de procédure stockée, comment puis-je gérer cela?
Si possible, j'aimerais que cela soit géré par la procédure stockée, plutôt que par le programme passant dans la chaîne.
3 réponses
Vous devez échapper les guillemets après avoir récupéré la valeur.
Et aussi COALESCE le paramètre au cas où un NULL est passé pour éviter l'erreur suivante
EXEC sp_executesql NULL
Ce qui donnerait
La procédure attend le paramètre «@statement» de type «ntext / nchar / nvarchar».
Ce qui donne les déclarations suivantes
DECLARE @Search nvarchar(100) = '';
SET @Search = REPLACE(COALESCE(@Search, ''), '''', '''''');
SET @LargeComplexQuery = 'SELECT * FROM People WHERE Name LIKE ''%' + @Search + '%'''
EXEC sys.sp_executesql @LargeComplexQuery
Ici en tant que version qui utilise les paramètres sp_executesql et qui n'est donc pas vulnérable à l'injection SQL - elle devrait également offrir de meilleures performances, pour citer MSDN:
Étant donné que l'instruction Transact-SQL elle-même reste constante et que seules les valeurs des paramètres changent, l'optimiseur de requêtes SQL Server est susceptible de réutiliser le plan d'exécution qu'il génère pour la première exécution.
CREATE PROCEDURE [dbo].[Yourproc]
(
@Search NVARCHAR(100) = N''
)
AS
DECLARE @LargeComplexQuery NVARCHAR(MAX) = 'SELECT * from People WHERE Name LIKE ''%'' + COALESCE(@Search, '''') + ''%'' ORDER BY Name'
EXEC sys.sp_executesql @LargeComplexQuery, N'@Search NVARCHAR(100)', @Search = @Search
J'ai fait certaines hypothèses, par exemple si vous passez une chaîne vide ou NULL comme condition de recherche, toutes les personnes sont renvoyées.
Tester - schéma factice et données:
CREATE TABLE People(Name NVARCHAR(MAX))
INSERT INTO People(Name)
VALUES ('Mr Smith'), ('Mrs Jones'), ('Miss O'' Jones')
Test de l'exécution du processus stocké:
DECLARE @search NVARCHAR(100) = N'Jones';
EXEC YourProc @Search; --Should get back both Mrs Jones and Miss O'Jones
SET @search = N'O'' Jones';
EXEC YourProc @Search; --Should get back just Miss O'Jones
SET @search = N'';
EXEC YourProc @Search; --Should get everyone, same as if you passed no search value at all
SET @search = NULL
EXEC YourProc @Search; --Should get everyone
Documentation MSDN sur sp_executesql
Soit échappez le guillemet dans l'application avant de passer le paramètre, soit faites-le dans le proc:
declare @Search nvarchar(100) = ''
declare @Search2 nvarchar(100)
declare @LargeComplexQuery nvarchar(max)
set @Search2 = replace(@Search, '''', '''''')
set @LargeComplexQuery = 'select * from People where Name like ''%' + @Search2 + '%''' -- I escaped the quote here too
exec sys.sp_executesql @LargeComplexQuery
De nouvelles questions
sql
Le langage de requête structuré (SQL) est un langage permettant d'interroger des bases de données. Les questions doivent inclure des exemples de code, une structure de table, des exemples de données et une balise pour l'implémentation du SGBD (par exemple MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) utilisés. Si votre question concerne uniquement un SGBD spécifique (utilise des extensions / fonctionnalités spécifiques), utilisez plutôt la balise de ce SGBD. Les réponses aux questions marquées avec SQL doivent utiliser le standard SQL ISO / IEC.
En Español