LIKE operator with $variable

问题:

This is my first question here and I hope it is simple enough to get a quick answer!

Basically, I have the following code:

$variable = curPageURL();
$query = 'SELECT * FROM `tablename` WHERE `columnname` LIKE '$variable' ;

If I echo the $variable, it prints the current page's url( which is a javascript on my page)

Ultimately, what I want, is to be able to make a search for which the search-term is the current page's url, with wildcards before and after. I am not sure if this is possible at all, or if I simply have a syntax error, because I get no errors, simply no result!

I tried :

    $query = 'SELECT * FROM `tablename` WHERE `columnname` LIKE '"echo $variable" ' ;

But again, I'm probably missing or using a misplaced ' " ; etc.

Please tell me what I'm doing wrong!

回答1:

Ultimately, what I want, is to be able to make a search for which the search-term is the current page's url, with wildcards before and after.

The SQL wildcard character is a percent sign. Therefore:

$variable = curPageURL();
$variable = mysql_real_escape_string($variable);
$query = "SELECT * FROM `tablename` WHERE `columnname` LIKE '%{$variable}%'";

Note: I've added in an extra bit of code. mysql_real_escape_string() will protect you from users deliberately or accidentally putting characters that will break your SQL statement. You're better off using parameterised queries, but that's a more involved topic than this simple fix.

Also note: I've fixed your string quoting, too. You can only use a variable in a string directly if that string is double quoted, and you were missing a quote at the end of $query.

edit 17 Jan 2015: Just got an upvote, so with that in mind, please don't use the mysql_* functions anymore.

回答2:

Use:

$query = "SELECT * FROM `tablename` WHERE `columnname` LIKE '{$variable}'" ;

To get an idea of why to prevent SQL injection attacks, like the above would be vulnerable to, I submit "Exploits of a Mom":

回答3:

Please don't do this, it is vulnerable to SQL injection (this is a list of 138 StackOverflow questions you should read, absorb and understand prior to returning to your application). Use parametrized queries or stored procedures.

回答4:

Use double quotes if you need to substitute variable values:

## this code is open for SQL injection attacks
$query = "SELECT * FROM `tablename` WHERE `columnname` LIKE '$variable'";

Or concat string manually:

## this code is open for SQL injection attacks
$query = 'SELECT * FROM `tablename` WHERE `columnname` LIKE "' . $variable . '"';

回答5:

Your code is vulnerable to SQL injection attacks. User-supplied data should never be placed directly into a SQL query string. Instead, it must first be sanitized with a function such as mysql_real_escape_string().

回答6:

As to why you're not being notified of the syntax error: It's fairly likely that your error reporting settings aren't set up correctly.

Open php.ini and make sure the following is set:

display_errors = On

And:

error_reporting = E_ALL