CODEWITHSUNDEEP
sql
PaulG
PaulG

Reputation: 7142

SQL wildcard issue

I have a database which can be modified by our users through an interface. For one field (companyID) they should have the ability to place an asterisk in the string as a wildcard character.

For example, they can put in G378* to stand for any companyID starting with G378.

Now on my client program I'm providing a "full" companyID as a parameter:

SELECT * FROM table WHERE companyID = '" + myCompanyID + "'

But I have to check for the wildcard, is there anything I can add to my query to check for this. I'm not sure how to explain it but it's kinda backwards from what I'm used to. Can I modify the value I provide (the full companyID) to match the wildcard value from in the query itself??

I hope this maked sense.

Thanks!

EDIT: The user is not using SELECT. The user is only using INSERT or UPDATE and THEY are the ones placing the * in the field. My program is using SELECT and I only have the full companyID (no asterisk).

Upvotes: 1

Views: 219

Answers (3)

TimothyAWiseman
TimothyAWiseman

Reputation: 14893

This is somewhat implementation dependant and you did not mention which type of SQL you are dealing with. However, looking at MS SQL Server wildcards include % (for any number of characters) or _ (for a single character). Wildcards are only evaluated as wildcards when used with "like" and not an = comparison. But you can pass in a paramater that includes a wildcard and have it evaluated as a wildcard as long as you are using "like"

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727137

This is a classic SQL Injection target! You should be glad that you found it now.

Back to your problem, when users enter '*', replace it with '%', and use LIKE instead of = in your query.

For example, when end-users enter "US*123", run this query:

SELECT * FROM table WHERE companyID LIKE @companyIdTemplate

set @companyIdTemplate parameter to "US%123", and run the query.

I used .NET's @ in the example, but query parameters are denoted in ways specific to your hosting language. For example, they become ? in Java. Check any DB programming tutorial on use of parameterized queries to find out how it's done in your system.

EDIT : If you would like to perform an insert based on a wildcard that specifies records in another table, you can do an insert-from-select, like this:

INSERT INTO CompanyNotes (CompanyId, Note)
   SELECT c.companyId, @NoteText
   FROM Company c
   WHERE c.companyId LIKE 'G378%'

This will insert a record with the value of the @NoteText parameter into CompanyNotes table for each company with the ID matching "G378%".

Upvotes: 3

Phil
Phil

Reputation: 497

in TSQL I would use replace and like. ie:

select * from table where companyid like replace(mycompanyid,'*','%');

Upvotes: 0

Related Questions