Reputation: 7142
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
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
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
Reputation: 497
in TSQL I would use replace and like. ie:
select * from table where companyid like replace(mycompanyid,'*','%');
Upvotes: 0