Little issue with search for a certain string

Question

I'm trying to search for rows that contain a certain string. For that, I'm using the following query:

SELECT `1` as 'msgTime',
       `4` as 'shout'
FROM   log.shout_log' WHERE shout LIKE '".$name."' ORDER BY 1 DESC

I'm getting $name from:

$name = $_GET['name'];

My url: ./shout_log.php?name=Montz

But I encounter the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' WHERE shout LIKE 'Montz' ORDER BY 1 DESC' at line 1

Everything is to return a message that this name shouted.

$ms = $row['shout'];
echo '<span style="color:blue;">'.$ms.'</span>';

Whats wrong with my query?

EDIT:

$name = "%".$_GET['name']."%";
    $info = mysql_query("SELECT `1` as 'msgTime', `4` as 'shout' FROM log.shout_log WHERE 'shout' LIKE '$name'") or die(mysql_error()); 
    echo '<table><tr><th>Data</th><th>Menssagem</th></tr>'; 
    while($row = mysql_fetch_object($info)){ 
        echo '<span style="color:blue;">'.htmlspecialchars($row->shout).'</span>'; 
    } 
    echo '</table>';

You don't have any wildcard characters, so `shout LIKE 'Montz'` is the same as `shout = 'Montz'`. It performs an exact match. — Barmar, Jul 14 '15 at 17:50
But in my db the data is like this: NAME: blablabla I need to get the name so I can return blablabla — João Monteiro, Jul 14 '15 at 17:50
Don't put backticks around `1` and `4`. Backticks are for column names, not literal values. If those really are the column names, that's just perverse. — Barmar, Jul 14 '15 at 17:51
You absolutely should not use `$_GET['name']` directly. Imagine a person putting in an evil name like `"; drop table user;" --`. — Micha Wiedenmann, Jul 14 '15 at 17:51
@MichaWiedenmann That won't do anything because `mysql_query` doesn't allow you to execute multiple queries. — Barmar, Jul 14 '15 at 17:52
have you tried using % like Barmar said. $name = "%".$_GET['name']."%"; Then in your sql use WHERE shout LIKE '$name' ORDER BY 1 DESC — Tim Strawbridge, Jul 14 '15 at 17:58
my table: http://i.imgur.com/iu9uWss.png (I can't post images) — João Monteiro, Jul 14 '15 at 18:10

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

As answered in the comments, you had an extra '.

Regardless, I wanted to point out that what you are doing is extremely dangerous since it is susceptible to SQL Injection attacks.

For example, if I say my name is:

  ';drop table `shout_log`; --

Then it will kill your database table.

Please see the following question for proper code to avoid SQL injection.

edited May 23 '17 at 11:43

Community

1
1

answered Jul 14 '15 at 18:14

Oz Solomon

2,969
23
22

I edited it and it still doesn't return anything, but thank you for that link – João Monteiro Jul 14 '15 at 18:16
I believe that's because you intended to write something like: `WHERE shout LIKE 'NAME: ".$name."' since your text string starts with NAME: – Oz Solomon Jul 14 '15 at 18:18
'Unknown column 'shout' in 'where clause' ' – João Monteiro Jul 14 '15 at 18:19
I don't know the names of the columns in your database. Substitute `shout` with the correct name. Before you had 'shout' in parentheses meaning it was comparing $name to the word 'shout'. I doubt that's what you intended. – Oz Solomon Jul 14 '15 at 19:18

andrew · Accepted Answer · 2015-07-14T18:43:39.647

0

You gave your column names aliases in your query, so:

$ms = $row['4'];

should be

 $ms = $row['shout'];

or

 $ms = $row['msgTime'];

whatever you were looking for, I don't even know, your numerical column naming strategy is ridiculous

EDIT

Ok so change:

WHERE shout LIKE

to:

WHERE `4` LIKE

EDIT2

ok my bad do this:

$query = "SELECT `1` as 'msgTime', `4` as 'shout' FROM log.shout_log WHERE 'shout' LIKE '$name'";
echo $query;
$info = mysql_query($query) or die(mysql_error());

edited Jul 14 '15 at 18:43

answered Jul 14 '15 at 18:22

andrew

9,313
7
30
61

Yes I know, its from a game source that I can't really change. But using $ms = $row['shout']; gives me the error: Unknown column 'shout' in 'where clause' – João Monteiro Jul 14 '15 at 18:24
edited again, now I get no errors, but no values either – João Monteiro Jul 14 '15 at 18:34
If I use 4, `4` , shout or '4' I get no errors, but no values :| – João Monteiro Jul 14 '15 at 18:36
SELECT `1` as 'msgTime', `4` as 'shout' FROM log.shout_log WHERE 'shout' LIKE '[GA]Montz', still no values .. – João Monteiro Jul 14 '15 at 18:53
ok well you're missing the percent signs from the like clause, it should be `LIKE '%[GA]Montz%'` but you have `$name = "%".$_GET['name']."%";` So it should work. I don't know what went wrong but that's where you should be looking – andrew Jul 14 '15 at 18:57
Yes, I tried with the % too, but it didn't work, so I removed it and paste it here, sorry. But it still didn't work SELECT `1` as 'msgTime', `4` as 'shout' FROM log.shout_log WHERE 'shout' LIKE '%[GA]Montz%' – João Monteiro Jul 14 '15 at 19:01
try `var_dump($row);` after `while($row = mysql_fetch_object($info)){ ` to see if you're getting anything back at all, sorry changed it to `$row` – andrew Jul 14 '15 at 19:03
SELECT `1` as 'msgTime', `4` as 'shout' FROM log.shout_log WHERE '4' LIKE '%[GA]Montz%' still nothing xD. also tried to do mysql_fetch_array on the while and I didn't get anything – João Monteiro Jul 14 '15 at 19:16
not 4 in single quotes!! should be 4 in backticks ` 4 ` (without spaces) – andrew Jul 14 '15 at 19:17
It finally worked, thank you, sorry for the stupid table names :D – João Monteiro Jul 14 '15 at 19:20

Little issue with search for a certain string

2 Answers2