LIKE operator with $variable

Question

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!

You'll find the solution in http://stackoverflow.com/questions/1318028/php-different-quotes — Ivan Nevostruev, Dec 03 '09 at 23:09

ceejayoz · Accepted Answer · 2015-01-17T19:22:53.750

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.

THANK YOU! You're a savior, thank you so much! I just noticed your edit, this is what I hadn't done!!It works fine now, thanks so much, and this site is definitly favorited. — skarama, Dec 03 '09 at 23:16

score 8 · Answer 2 · edited Feb 08 '17 at 14:07

8

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":

alt text

edited Feb 08 '17 at 14:07

Community

1
1

answered Dec 03 '09 at 22:58

OMG Ponies

325,700
82
523
502

I get the idea but, the fields tablename and columnname aren't actual fieldnames, I changed the real ones. Or am I getting wrong? And I'm not taking offense, I'm quite happy I'm getting so many quick answers! – skarama Dec 03 '09 at 23:04
skarama: He means that you shouldn't create SQL queries using strings to enter parameters. By doing that, you're giving your users full access to your database (with the same permissions as your webserver). – Mark Byers Dec 03 '09 at 23:08
@Skarma: The point of SQL Injection attack is that it artificially terminates the string parameter you're attempting to populate, and then submit a second query immediately afterwards that is likely to be malicious. Doesn't matter what happens in the first query. – OMG Ponies Dec 03 '09 at 23:10
-1 for a non-working query. The variable won't be evaluated in a single-quoted string, it needs single quotes around the variable itself, and you're missing the wildcards the user requested. – ceejayoz Dec 03 '09 at 23:13
Thank you everyone, I will also take a look at SQL Injection attacks and try to learn how to prevent it :) – skarama Dec 04 '09 at 00:20

score 5 · Answer 3 · edited May 23 '17 at 12:17

5

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.

edited May 23 '17 at 12:17

Community

1
1

answered Dec 03 '09 at 22:58

Cade Roux

88,164
40
182
265

I am afraid I have no idea what this means, I am more than a newbie at this :( – skarama Dec 03 '09 at 22:59
3

This doesn't really help, at all. – Jeffrey Aylesworth Dec 03 '09 at 22:59
2

+1: Someone took offense to being warned about bad practices :/ – OMG Ponies Dec 03 '09 at 23:00
4

Warning about bad practices is useless to a newbie if you don't explain what you're talking about and help show how to fix it. – ceejayoz Dec 03 '09 at 23:03

score 2 · Answer 4 · answered Dec 03 '09 at 23:05

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 . '"';

score 1 · Answer 5 · answered Dec 03 '09 at 23:36

1

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().

answered Dec 03 '09 at 23:36

streetparade

32,000
37
101
123

score 0 · Answer 6 · answered Dec 03 '09 at 23:19

0

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

answered Dec 03 '09 at 23:19

Paul Lammertsma

37,593
16
136
187

LIKE operator with $variable

6 Answers6

Linked