wake-up-neo.com

Entziehen Sie eine Zeichenfolge in SQL Server, damit die Verwendung in LIKE-Ausdrücken sicher ist

Wie kann ich eine Zeichenfolge in der gespeicherten Prozedur von SQL Server maskieren, damit sie sicher im Ausdruck LIKE verwendet werden kann?.

Angenommen, ich habe eine NVARCHAR -Variable wie folgt:

declare @myString NVARCHAR(100);

Und ich möchte es in einem LIKE Ausdruck verwenden:

... WHERE ... LIKE '%' + @myString + '%';

Wie kann ich die Zeichenfolge umgehen (genauer gesagt, Zeichen, die für die LIKE Mustererkennung von Bedeutung sind, z. B. % oder ?) in T-SQL, damit die Verwendung auf diese Weise sicher ist?

Zum Beispiel gegeben:

@myString = 'aa%bb'

Ich möchte:

WHERE ... LIKE '%' + @somehowEscapedMyString + '%'

passen 'aa%bb', 'caa%bbc' aber nicht 'aaxbb' oder 'caaxbb'.

sql-servertsqlstored-proceduressql-like
74
Artur

Um Sonderzeichen in einem LIKE-Ausdruck zu maskieren, wird ihnen ein Escape-Zeichen vorangestellt. Sie können auswählen, welches Escapezeichen mit dem ESCAPE-Schlüsselwort verwendet werden soll. ( MSDN Ref )

Dies maskiert beispielsweise das% -Symbol mit\als Escapezeichen:

select * from table where myfield like '%15\% off%' ESCAPE '\'

Wenn Sie nicht wissen, welche Zeichen in Ihrer Zeichenfolge enthalten sein werden, und Sie sie nicht als Platzhalter behandeln möchten, können Sie allen Platzhalterzeichen ein Escapezeichen voranstellen, z.

set @myString = replace( 
                replace( 
                replace( 
                replace( @myString
                ,    '\', '\\' )
                ,    '%', '\%' )
                ,    '_', '\_' )
                ,    '[', '\[' )

(Beachten Sie, dass Sie auch Ihrem Escape-Zeichen entkommen müssen und sicherstellen müssen, dass dies das innere replace ist, damit Sie nicht den Anweisungen entgehen, die aus den anderen replace -Anweisungen hinzugefügt wurden.) Dann können Sie so etwas verwenden:

select * from table where myfield like '%' + @myString + '%' ESCAPE '\'

Denken Sie auch daran, mehr Platz für Ihre @ myString-Variable zu reservieren, da diese mit dem Ersetzen der Zeichenfolge länger wird.

90
Rory

Hatte ein ähnliches Problem (mit NHibernate, also wäre das ESCAPE-Schlüsselwort sehr schwierig gewesen) und löste es mit den Klammerzeichen. So würde Ihre Probe werden

WHERE ... LIKE '%aa[%]bb%'

Wenn Sie Beweise brauchen:

create table test (field nvarchar(100))
go
insert test values ('abcdef%hijklm')
insert test values ('abcdefghijklm')
go
select * from test where field like 'abcdef[%]hijklm'
go
18
Dries Van Hansewijck

Anstatt all Zeichen in einer Zeichenfolge zu maskieren, die in der Mustersyntax eine besondere Bedeutung haben, da Sie im Muster einen führenden Platzhalter verwenden, ist dies schneller und einfacher.

SELECT * 
FROM YourTable
WHERE CHARINDEX(@myString , YourColumn) > 0

In Fällen, in denen Sie keinen führenden Platzhalter verwenden, sollte der oben beschriebene Ansatz jedoch vermieden werden, da kein Index für YourColumn verwendet werden kann.

In Fällen, in denen der optimale Ausführungsplan von der Anzahl der übereinstimmenden Zeilen abhängt, sind die Schätzungen möglicherweise besser, wenn LIKE verwendet wird und die eckige Klammer im Vergleich zu beide CHARINDEX nicht angegeben wird. und das Schlüsselwort ESCAPE .

13
Martin Smith

Sie geben das Escapezeichen an. Dokumentation hier:
http://msdn.Microsoft.com/en-us/library/ms179859.aspx

4
Corey Trager

Möchten Sie nach Zeichenfolgen suchen, die ein Escapezeichen enthalten? Zum Beispiel möchten Sie dies:

select * from table where myfield like '%10%%'.

Wo möchten Sie mit 10% nach allen Feldern suchen? In diesem Fall können Sie die ESCAPE-Klausel verwenden, um ein Escape-Zeichen anzugeben und das Platzhalterzeichen zu maskieren.

select * from table where myfield like '%10!%%' ESCAPE '!'
3
Vincent Ramdhanie

Alternative Escape-Syntax:

WIE Wildcard-Literale

Der JDBC-Treiber unterstützt die Syntax {escape 'escape character'} für die Verwendung von LIKE-Klausel-Platzhaltern als Literale.

SELECT *
FROM tab
WHERE col LIKE 'a\_c' {escape '\'};

db <> fiddle demo

0
Lukasz Szozda