In either case a more detailed description would be helpful for all concerned.
Thanks for the help.
Tim Rawley wrote:"old cat" would search for old and cat from all tables not just the string "old cat".
Sorry, I still don't understand the requirement. Are you looking for rows in which some column contains the string "old" and the string "cat"? Or are you looking for rows in which some column contains the string "old" or the string "cat"? Or rows in which some column contains the word "old" and another column contains the word "cat"? Or maybe rows in which some column contains the word "old" or some other column contains the word "cat"?
As a programmer you're going to be given instructions which are unclear from time to time. So you need to be able to recognize unclear instructions so you can go back and ask for clarification. What you've provided here is an unclear instruction; one clue which tells you that is that you have no idea how to implement it. Sure, sometimes that's because you need to learn more about something, but often it's because you don't know exactly what's being asked for. In this case it's certainly the latter.
Tim Rawley wrote:... search for all words in my search box. "old cat" would search for old and cat from all tables not just the string "old cat".
I think you mean "search for old
I don't know which RDBMS you are using, but MySQL has a REGEXP operator which matches based on regular expressions. If you build a regular expression out of your search words, you could do something like :
SELECT * FROM vipreserial WHERE first REGEXP 'old|cat|this|that|another' OR last REGEXP 'old|cat|this|that|another' OR ...
Also could someone really get more info other than what is already being offered?
The query is already pointing to the columns that they are allowed to search, so what more could they do?
Again, thanks for the info.
Tim Rawley wrote:... so what more could they do?
Say you wanted to search for names, and one was:
Your current one would fail, whereas one that used a PreparedStatement and bound the parameters would handle it.
Finally there's also a performance advantage with many DBs.
Databases tend to store plans for common queries, but the mapping is done on the text of the statement itself so:
SELECT columnA from some_table WHERE columnB = 'hardcoded value'
is a different query to:
SELECT columnA from some_table WHERE columnB = 'another hardcoded value'
resulting a individual plans being created, even though they would likely be the same.
With a PreparedStatement the query plan would be:
SELECT columnA from some_table WHERE columnB = ?
so both would use the same plan, saving what can be a lot of time for complicated queries to come up with an effective plan.
So it's not just about security.
Tim Rawley wrote:so what more could they do?
Famous last words...
They could for example search on a word containing a quote (e.g. Domino's Pizza). And instead of returning all matching rows, you'll get a BadSqlGrammarException being thrown at runtime and you'll be wondering why because it worked for "smelly old cat" When writing queries you should always consider using prepared statements in order to prevent possible SQL injection attacks, to have character escaping out of the box and to have (slightly) better performance (as already explained by Dave). More info about prepared statements can be found here.
Hope it helps!