Introduction to SQL Server Function to Perform Fuzzy Search

Summary

it is normal to filter data on a daily site. This method applies to filtering like, if, CASE, in other words, the injection does not work properly, but the page does not show back.

Replacement Method
DescriptionPATINDEX(‘%pattern%’, expression)
returns the position where the pattern string first appears in the expression. The starting value starts from 1. , 0 is not returned

this function is highly consistent with like and can be the same as like
use_% [] [^] search for wildcards
CHARINDEX(‘pattern’, expression)

returns the position where the pattern string first appears in the expression. The starting value starts from 1. , 0 is not returned

Test Data

1> select * from article;
2> go
3+----+-----------+-----------+
4| id | title       | content    |
5+----+-----------+-----------+
6|  1 | Test Title | Test Content |
7|  2 | Test Title 2 | Test Content 2 |
8+----+-----------+-----------+
9(2 rows affected)
1 # Test table data users;
2
3 sql server> select * from users;
4 +----+--------------+----------+
5 | id | username     | password |
6 +----+--------------+----------+
7 |  1 | test-user-01 |  123456  |
8 |  2 | test-user-02 |  234567  |
9 +----+--------------+----------+
10 2 rows in set (0.00 sec)
1 sql server> SELECT system_user;
2 +-----------------------+
3 | field1                |
4 +-----------------------+
5 | sa                    |
6 +-----------------------+
7 1 row in set (0.00 sec)
1 sql server> select db_name();
2 +-----------------------+
3 | field1                |
4 +-----------------------+
5 | test                  |
6 +-----------------------+
7 1 row in set (0.00 sec)

PATINDEX()

Inquire user

SQL：select ‘test’ where patindex(‘%sa%’, system_user)>=1;

1 # system_user = sa
2
3 # the right situation
4 1> select 'test' where patindex('%sa%', system_user)>=1;
5 2> go
6 +-----+
7 |     |
8 +-----+
9 | test |
10 +-----+
11 (1 rows affected)
12
13 # error case
​14 1> select 'test' where patindex('%aaa%', system_user)>=1;
15 2> go
16 +--+
17 |  |
18 +--++--+(0 rows affected)

Query Table Name

note:

The name in OVER(Order by table_name) should be changed to a field in the test.dbo.sysobjects table

Querying different libraries can be done like this

For example, there are now test libraries and test2 libraries. Then you can call like this

• test.dbo.sysobjects
• test2.dbo.sysobjects

Querying different tables can be done like this

E.g:

• Modify row_number>=1
• Modify row_number>=2

Notice:

• XType=’U’ means to get all user tables of a database;
• XType=’S’ means to get all system tables of a database;

For example, now the query is the table name of the test library

SQL：select ‘test’ where patindex(‘%article%’, (select name from (select ROW_NUMBER() OVER(Order by name) AS row_number,name FROM test.dbo.sysobjects Where XType=’U’) as a where row_number=1))>=1

1 # first table name = article
2
3 # the right situation
4 1> SELECT
5 'test'
6 WHERE
7 patindex(
8 '%article%',
9 (
10 SELECT 
11 name
12 FROM
13 (
14 SELECT
15 ROW_NUMBER () OVER (ORDER BY name) AS row_number,
16 name
17 FROM
18 test.dbo.sysobjects
19 WHERE
20 XType = 'U'
21 ) AS a
22 WHERE
23 row_number = 1
24 )
25 ) >= 1;
26 2> go
27 +-----+
28 |     |
29 +-----+
30 | test |
31 +-----+
32 (1 rows affected)
33​
34​
35​
36 # wrong situation
37 1> SELECT
38 'test'
39 WHERE
40 patindex(
41 '%aaaaaaaaaaaaaa%',
42 (
43 SELECT
44 name
45 FROM
46 (
47 SELECT
48 ROW_NUMBER () OVER (ORDER BY name) AS row_number,
49 name
50 FROM
51 test.dbo.sysobjects
52 WHERE
53 XType = 'U'
54 ) AS a
55 WHERE
56 row_number = 1
57 )
58 ) >= 1;
59 2> go
60 +--+
61 |  |
62 +--+
63 +--+
64 (0 rows affected)

CHARINDEX()

query user

SQL：select ‘test’ where charindex(‘s’, system_user)>=1

1 # system_user = sa
2
3 # right case
4 # system_user first character
5 1> select 'test' where charindex('s', system_user)>=1
6 2> go
7 +-----+
8 |     |
9 +-----+
10 | test |
11 +-----+
12 (1 rows affected)
13
14 # system_user second character
15 1> select 'test' where charindex('sa', system_user)>=1
16 2> go
17 +-----+
18 |     |
19 +-----+
20 | test |
21 +-----+
22 (1 rows affected)
23
​24
25 # wrong situation
26 1> select 'test' where charindex('aaaaa', system_user)>=1
27 2> go
28 +--+
29 |  |
30 +--+
31 +--+
32 (0 rows affected)

query table name

SQL：select ‘test’ where charindex(‘ar’, (select name from (select ROW_NUMBER() OVER(Order by name) AS row_number,name FROM test.dbo.sysobjects Where XType=’U’) as a where row_number=1))>=1

1 # first table name = articl
2
3 # the right situation
4 # Query the first 1-2 characters
5 1> SELECT
6 'test'
7 WHERE
8 charindex(
9 'ar',
10 (
11 SELE
12 name
13 FROM 
14 (
15 SELECT
16 ROW_NUMBER () OVER (ORDER BY name) AS row_number,
17 name
18 FROM
19 test.dbo.sysobjects
20 WHERE
21 XType = 'U'
22 ) AS a
23 WHERE 
24 row_number = 1
25 )
26 ) >= 1;
27 2> go
28 +-----+
29 |     |
30 +-----+
31 | test |
32 +-----+
33 (1 rows affected)
34
35
36 # Query 1-4 characters
37 1> SELECT
38 'test'
39 WHERE 
40 charindex(
41 'arti',
42 (
43 SELECT
44 name
45 FROM
46 (
47 SELECT
48 ROW_NUMBER () OVER (ORDER BY name) AS row_number,
49 name 
50 FROM 
51 test.dbo.sysobjects
52 WHERE 
53 XType = 'U'
54 ) AS a 
55 WHERE
56 row_number = 1
57 )
58 ) >= 1;
59 2> go
60 +-----+
61 |     |
62 +-----+
63 | te|
64 +-----+

The Original Article is Published at TUTORIALBOY