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
Josh B 21 avril 2017 à 11:00

3 réponses

Meilleure réponse

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
3
Hybris95 21 avril 2017 à 08:29

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

2
Bridge 21 avril 2017 à 08:58

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
4
JohnHC 21 avril 2017 à 08:10