SQL or PHP Syntax?

Anon

Below is a form page and a result.php page.

I am getting the error:

Error: You have an error in your SQL syntax near 'Resource id #2' at line 1

When I use the MySQL monitor, it returns the correct result with:

"select * from Clients where Industry = 'Energy';

So it has to be something wrong with the syntax on the .php result page.

<head>
<title>4i dotCom Client Search</title>
</head>

<body>
<h1>Client Search</h1>

<form action="http://localhost/client_results.php" method="post">
Choose Search Type:<br>
<select name="searchtype">
<option value="Industry">Industry
<option value="ClientName">Name
<option value="ClientPostCode">Post Code
<option value="ClientStName">Street
</select>
<br>
Enter Search Text:<br>
<input name="searchterm" type=text>
<br>
<input type=submit value="Search">
</form>

</body>

HERE IS THE RESULTS.PHP PAGE

<head>
<title>4i dotCom Client Search</title>
</head>
<body>
<h1>4i dotCom Client Search</h1>
<?
trim($searchterm);
if (!$searchtype || !$searchterm)
{
echo "You have not entered search details. Please go back and try again.";
exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

$db = @mysql_pconnect("localhost.localdomain:3306","root","password");

if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db("4i_dotCom");
$query = "select * from Clients where ".$searchtype." like '%".$searchterm."%'";
$result = mysql_query($query);
$result = mysql_query($result) or die("Error: ".mysql_error());
$num_results = mysql_num_rows($result);

echo "<p>Number of entries found: ".$num_results."</p>";

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<p><strong>".($i+1).". Industry: ";
echo htmlspecialchars( stripslashes($row["Industry"]));
echo "</strong><br>Name: ";
echo htmlspecialchars( stripslashes($row["ClientName"]));
echo "<br>Post Code: ";
echo htmlspecialchars( stripslashes($row["ClientPostCode"]));
echo "<br>Street: ";
echo htmlspecialchars( stripslashes($row["ClientStreetName"]));

echo "</p>";
}

</body>

Anon

This line is incorrect.

$result = mysql_query($result) or die("Error: ".mysql_error());

I think you want to use mysql_fetch_array() instead.

Anon

Hi Scott,

the error is:

"$result = mysql_query($query);
$result = mysql_query($result) or die("Error: ".mysql_error());"

you try to get a result with the result-handle!
try this:
"$result = mysql_query($query) or die("Error: ".mysql_error()); "

hope that helps
chris

by the way - found this in the php-manual on php.net (your script trusts the variable 'searchterm' - see what might happen):

"Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is
a trusted command. It means that SQL queries are able to circumvent access controls, thereby bypassing
standard authentication and authorization checks, and sometimes SQL queries even may allow access to host
operating system level commands.

                           Direct SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to
                           expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the
                           database host. This is accomplished by the application taking user input and combining it with static parameters
                           to build a SQL query. The following examples are based on true stories, unfortunately. 

                           Owing to the lack of input validation and connecting to the database on behalf of a superuser or the one who can
                           create users, the attacker may create a superuser in your database. 
                           Example 4-6. Splitting the result set into pages ... and making superusers (PostgreSQL and MySQL) 

                            $offset = argv[0]; // beware, no input validation!
                            $query  = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
                            // with PostgreSQL 
                            $result = pg_exec($conn, $query);
                            // with MySQL
                            $result = mysql_query($query);



                           Normal users click on the 'next', 'prev' links where the $offset is encoded into the URL. The script expects that
                           the incoming $offset is decimal number. However, someone tries to break in with appending urlencode()'d form
                           of the following to the URL 

                            // in case of PostgreSQL
                            0;
                            insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
                                select 'crack', usesysid, 't','t','crack'
                                from pg_shadow where usename='postgres';
                            --

                            // in case of MySQL
                            0;
                            UPDATE user SET Password=PASSWORD('crack') WHERE user='root';
                            FLUSH PRIVILEGES;



                           If it happened, then the script would present a superuser access to him. Note that 0; is to supply a valid offset to
                           the original query and to terminate it. 

                                 Note: It is common technique to force the SQL parser to ignore the rest of the query written by the
                                 developer with -- which is the comment sign in SQL."

Anon

oops, i didn't see that you are using the mysql_fetch_array() function.

Just eliminate the second redundant query and it should work.