Issue with SQL on connectors

Support, Troubleshooting, & Help
1

I’m pulling data from an SQL database, with an input of part number which is in the format of X-NNNN-NNNN sometimes with a suffix of -NN or -XXX, and i want to ignore that suffix.
My SQL querie is as follows:
select *
from [MPD_Service].[dbo].[Configuration]
where [Part_Number] LIKE $Part_Number$+’%’

Part_Number in the database is always X-NNNN-NNNN and the input might be things such as:
X-NNNN-NNNN or,
X-NNNN-NNNN-NN or,
X-NNNN-NNNN-XXX

In my understanding the ‘+’%’ wildcard should work, but its not. Only the first case above works, the other 2 return ‘null’

2

You typically don’t want to concat values into dynamic SQL strings because of SQL injection.

This is my favorite example (its a postgresql fiddle, but gets the point across)
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=6fe89b1a19f74439ea637fe905ad3af6

So it looks like for SQL server (I’m assuming that’s the connection)
You should use sp_executesql

Executing a simple SELECT statement

The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level .

SQLCopy

EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorks2012.HumanResources.Employee   
          WHERE BusinessEntityID = @level',  
          N'@level TINYINT',  
          @level = 109;
3

Sorry but i don’t see how your suggestion relates to my issue.
After some testing in SQLFiddle.com i managed to find a solution that tests out ok there:
SELECT * FROM [Configuration] WHERE partNo LIKE LEFT(@Input,11)
but that does not work in Tulip.

4

Actually, on second thought I think Tulip sanitizes the query ( I should ask about that). What DB are you connecting to?

5

MS SQL Server (unsure of version, can check)

6

One thing to try is tick marks around your variable?

select *
from [MPD_Service].[dbo].[Configuration]
where [Part_Number] LIKE ‘$Part_Number$’+’%’

I was playing with it here at the mssql fiddle SQL Test

7

In fact the solution is: where [Part_Number] LIKE LEFT($Part_Number$,11)+’%’
I was almost there, but thank you Tulip support for giving the final answer.

1 Like