Query parameterized but '[' not giving search-results. VB.NET Framework 4. Entity to ESQL

Question

The following are company name in my database. I am trying to produce on the fly string query that are foolproof against SQL Injection.

I test the safety tolerance level of parameterized query against SQL Injection because I hear rumors it is not 100%.

=====userinput pure strings but with malicious content for SQL injection=========

userinput = "Tubo'; UPDATE STOCK_TYPE SET Description='SQL Injection'" & 
            "WHERE STock_Type_ID=1; --'"

userinput = "'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over!" &
            "This system is no longer yours!''" & 
            "-- Malicious User now can control the database!!!'"

userinput = "!!!@@@###$$$%%%^^^&&&***((()))///```~~~___---+++===:::;;;"""'''|||
             \\\}}}>>><<<{{{}}}]]]...,,,[[[]]]///"

=====To search company name with SQL Injected userinput ==========================

Dim SQL = "select p.Name from EntContext.COMPANIes as p Where p.Name like @CompanyName"

ObjectParamters.Add(New ObjectParameter("@CompanyName", userinput))

Dim databaseObjectQuery As ObjectQuery(Of DbDataRecord) = 
     New ObjectQuery(Of DbDataRecord)(SQL, EntContext)

databaseObjectQuery.Parameters.Add(parameter)

====================================================================

COMPANIes.name database contain exactly these strings to help my testing. By a successful search against user input I think my code is stronger against attack.

But when I search against COMPANIes.name with the exact user input string, only the first userinput produced result.

I narrow the culprit to '[' it doesn't produce result for second and third userinput via LINQPAD

select p.company_id , p.Name
from EntContext.COMPANIes as p
Where  p.Name like '%[%'

No result. So do this

select p.company_id , p.Name
from EntContext.COMPANIes as p
Where  p.Name like '%/[%' escape '/'

produce the result against second and third user input over matching '[' in company name.

================Attempt to solve programmatically in VB.NET ===============

FilterSQL = " AND COMPANies.Name like @CompanyName escape " + Chr(255)
objectParameters.Add(New ObjectParameter("CompanyName", "%" & Chr(255) &
searchResultParam.ParamValue.Value & "%"))


 The simple identifier 'ÿ' must contain basic Latin characters only. 
 To use UNICODE characters, use an escaped identifier. Near line 1, column 320.

=============================================================================

1. Are parameterized query 100% foolproof even userinput is coded like this?

   ObjectParamters.Add(New ObjectParameter("@CompanyName", userinput))

2. Was my assumption over second and third userinput correct on escaping this character '[' ?

3. In parameterized query of LINQ, how do search against this exact stored value?

   COMPANIes.Name= "!@#$%^&*()/`~_-+=:;"'|}><{}].,[]/"

Answer 1

A breakthrough with this code

FilterSQL = " AND COMPANies.Name like @CompanyName escape '['"
Dim escCrit As String = "%" & ParamValue.Value.Replace("[", "[[") & "%"
objectParameters.Add(New ObjectParameter("CompanyName", escCrit ))

Here's actual and literal ID & Name stored in my COMPANies.Name column.

1. Tubo'; UPDATE STOCK_TYPE SET Description='SQL Injection' WHERE STock_Type_ID=1; --'

2. 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!''-- Malicious User now can control the database!!!'

3. !!!@@@###$$$%%%^^^&&&*((()))///```~~~_---+++===:::;;;"""'''|||\}}}>>><<<{{{}}}]]]...,,,[[[]]]///???'''

4. ?!@#$%^&*()/`~_-+=:;"'|}><{}].,[]/ȸ

============================================================

Enter userinput in textbox and click Search. Test results:

userinput = Tubo'; UPDATE STOCK_TYPE SET Description='SQL Injection' WHERE STock_Type_ID=1; --'

Company ID 1 found

userinput = 'Some Name''; GRANT CONTROL TO [Malicious User]; PRINT ''Game over! This system is no longer yours!'' -- Malicious User now can control the database!!!' 

Company ID 2 found

userinput = !!!@@@###$$$%%%^^^&&&***((()))///```~~~___---+++===:::;;;"""'''||| 
         \\\}}}>>><<<{{{}}}]]]...,,,[[[]]]/// 

Company ID 3 found

userinput = ?!@#$%^&*()/`~_-+=:;"'|}><{}].,[]/ȸ

Company ID 5 found

userinput = [

Company ID 2, 3 and 5 found

userinput = []

Company ID 3 and 5 found

userinput = [[[

Company ID 3 found

This answers my third question. I did not have to filter any input and not necessary limit to textbox search criteria only. This mode accomodate even special characters, especially useful in the case of storing public comments from your users in your database. They could type anything in their text block. I don't know why '[' needs to be specifically code as an escape. I think it's a bug, the entity framework team forgot to escape '['. They do however escape everything else I have tested so far. Note the character ȸ Latin Small Letter Db Digraph is intentional.

I wonder what magic happened. I think the SQL is sent separately from the parameter. When SQL never meet parameter, we can never get SQL Injection. Impossible because the modality of SQL Injection depend on dirty SQL. But SQL never met Injection. My test case on range of special characters suggests this.

I think the implementation implicitly use sp_executesql at the backend from LINQ. In the backend, the SQL goes through execution planning while parameters were matched as string literal. I just have to be careful that my SQL never 'meet' user input whether from frontend or database backend.