4 replies to this topic

[SimonCoder]

When I am creating a VB app and connect to an Access database, I can create an SQL Query that will return certain rows based on a "Like" query; for instance if I use

Like ? + %

in the filter query, I can use "RE" to return "READ" and "REED" and "READY".

My question is, I've tried the same code when connected to MySQL, but it does not return the same values, so what is the LIKE query that works for MySQL?

[gregwarner]

Here's how I'd do it in MySQL:

SELECT col_name FROM table_name WHERE col_name LIKE 'RE%';

The '%' character represents zero or more of any character. This statement will match all strings beginning with 'RE', case insensitive, I think.

[HighKing Scott]

Yes and that would work, but what if you wanted the end-user of your application to supply the 'RE' (which is my problem) I want the query setup so that the user enters RE in a textbox, then clicks a button and the query will return all the results in the database that begin with RE (or whatever the user inputs in the textbox)

edit: I have found that if you setup your query filter like so: LIKE @PARAM1
then the end user can enter RE% in the textbox and I will get the result I'm looking for. But if this were Access and the query filter was LIKE ? + %, then the end user could just enter RE (without the % sign) and it would return the same result.

[wim DC]

When throwing user input in a query for a database, it should always be encoded somehow so all user input will never be interpreted as part of the query, but only as parameter. Dependant on the programming language there's always(usually) some kind of prepared query, or prepared statement which allows you to declare the query up front, and indicate where variables will come. Later you can fill in the variables and the code should do all of the required work.

If you don't do it in some way like this, you're vulnerable to SQL injection (google it)

[fayyazlodhi]

100% support to creating through query string rather than direct params.

A plain harsh e.g. to get those who don't have idea of sql injection:

SQL Injection Attacks by Example

Your entire database can be deleted, obtained, corrupted depending upon the type of exploit.

php - when/where we use PDO? - Stack Overflow