0

I'm trying to search a MySQL database for the user specific website activity, to do this I created a form and process to search through a statistics table and return every record with a userID matching the query. However I keep getting this message and not sure why:

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 '@hotmail.com' at line 1

This is the form I'm using

<form method='get' action='searchuser.php'  id='searchuser'>
<input type='text' input name='txtSearch' id='txtSearch'>
    <input type='submit' name='submit' value='Search'>
</form>";

and this is the process file

<?php
require_once( "Functions.php" );
$header = makeHeader();

$con= connect();


$user = $_GET['txtSearch'];

$query = "SELECT * FROM statistics WHERE userID = $user";
$result=mysql_query($query) or die (mysql_error());

echo"<table border='1'><th>User</th><th>IP</th>
    <th>Date</th><th>Page visited</th><th>Page from</th>";

while($row = mysql_fetch_assoc($result))
{
$username = $row['userID'];
$ip =  $row['ipAddress'];
$date = $row['dateOfVisit'];
$pagev = $row['pageVisited'];
$pagef = $row['pageFrom'];

echo "<tr><td>".$row->UserID."</td><td>".$row->ipAddress."</td><td>".$row->dateOfVisit."</td><td>".
    $row->pageVisited."</td><td>".$row->pageFrom . "<br/>\n"."</td></tr>";
    }
IF (mysql_num_rows($queryresult) == "") 
            {
                Echo "<p>Sorry there were no results for your         search<p> <br /><br /> <p><A HREF='javascript:javascript:history.go(-1)'>Click here to go back to previous page</A></p>";
            } 


$footer = makeFooter();

?>
Marc Alff
  • 8,227
  • 33
  • 59
user1302775
  • 195
  • 1
  • 2
  • 6
  • 1
    Your code is vulnerable to trivial SQL injection. You should fix this. – Romain Apr 20 '12 at 12:38
  • possible duplicate of [How to include a PHP variable inside a mysql insert statement](http://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-insert-statement) – Your Common Sense Apr 20 '12 at 13:13

2 Answers2

3

The SQL problem was with

userID = $user

The correct way of putting a string into sql query is

  • to delimit it with quotes
  • and use mysql_real_escape_string to escape possible delimiters that may happen inside.

So. the right code would be

  $user =  mysql_real_escape_string($user);
  $query = "SELECT * FROM statistics WHERE userID = '$user'";
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
ab_dev86
  • 1,952
  • 16
  • 21
  • 1
    While this works, if you are not going to add anything about MySQL injection, prepared statements or escaping, you deserve to get downvoted. – Konerak Apr 20 '12 at 12:39
  • As Romain suggest clean $user before injecting it into the sql string: http://php.net/manual/en/function.mysql-real-escape-string.php – ab_dev86 Apr 20 '12 at 12:40
  • @ab_dev86: better (removed downvote)- can you now explain a bit why you added that line (get upvote)? – Konerak Apr 20 '12 at 12:47
  • all that rubbish again. escaping has nothing to do with injections. it is just the same formatting rule as adding quotes. – Your Common Sense Apr 20 '12 at 13:14
  • so how to clean up $user to avoid injection? – ab_dev86 Apr 20 '12 at 13:19
  • there is no need to clean anything. one have to always format their strings properly. A properly formatted string is invulnerable to injections. – Your Common Sense Apr 20 '12 at 13:20
  • @Your Common Sense: one have to always format their strings properly. Ok so in this pratical case how to be sure that $user coming from user http get isn't dangerous for sql syntax? the code currently in my answer is enough? thanks – ab_dev86 Apr 20 '12 at 13:46
-2

try this one

$user = $_GET['txtSearch'];
$query = "SELECT * FROM statistics WHERE userID = ".$user." ";

this working for me

prakash
  • 184
  • 2
  • 10
  • 2
    This doesn't work if userID is a text-based field, as it is for the OP. It also does not work if there are any spaces in the input. It's also vulnerable to SQL Injection. The OPs code would work under your circumstances. -1 – Andrew Barber Apr 20 '12 at 12:58