Page 1 of 1

error in SQL syntax!!!

Posted: Wed Jul 18, 2007 12:38 pm
by xiao_dolp
Hey!!
I have error to add information for one of my database. I try to echo out the $sql to see whether it will echo out i try, and it display.After i try to find the mysql_error and it display this
INSERT INTO place ( id, from, place, to, description ) VALUES ( '55', 'D', 'D', 'D', 'D')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 'from, 'place', 'to', 'description' ) VALUES ( '55', 'D', 'D', 'D', 'D')' at line 1
i not sure what is the error, as for other page, i aso use about the same code, the only different is the name of the database and variable name and i able to insert into database. but i not sure why this is not working??? please help!!!

This is my php code

Code: Select all

<?php
require('db.php');
//retrieve the information from html form

$d = $_POST['id'];
$from = $_POST['from'];
$place = $_POST['place'];
$to = $_POST['to'];
$description = $_POST['description'];

//connect to DB server


mysql_connect(MACHINE, USER, '');
mysql_select_db(DBNAME);

//prepare the sql query
$sql = "INSERT INTO place ( id, from, place, to, description ) VALUES ( '".$id."', '".$from."', '".$place."', '".$to."', '".$description."')";
echo $sql;
// mysql_query($sql);
mysql_query($sql) or die (mysql_error());

echo "<a href = 'try.php'>Go back to the  page</a>";
?>

Posted: Wed Jul 18, 2007 1:08 pm
by superdezign
"FROM" is a reserved word. Surround it in backticks (`).

Posted: Wed Jul 18, 2007 1:36 pm
by xiao_dolp
u mean like that???
$sql = "INSERT INTO place ( id, from', place, to, description ) VALUES ( '".$id."', '".$from."', '".$place."', '".$to."', '".$description."')";

it still show the same error no matter how i place the ' '

Posted: Wed Jul 18, 2007 1:45 pm
by superdezign
Backticks (`) and single quotes (') are different.

The backtick (`) is to the left of the '1' on your keyboard.

Posted: Wed Jul 18, 2007 2:10 pm
by xiao_dolp
ok!!thank!!it able to added into my database!!!

but my update php aso have this problem and i put ` ` to the code but it echo failed

Code: Select all

<html>
<head>
</head>

<body>
<?
//connect to DB server
require('db.php');
//retrieve the information from html form

$id = $_POST['id'];
$from = $_POST['from'];
$place = $_POST['place'];
$to = $_POST['to'];
$description = $_POST['description'];

echo $id;
echo $from;
echo $place;
echo $to;
echo $description;


mysql_connect(MACHINE, USER, '');
mysql_select_db(DBNAME);


//prepare the sql query



$sql = "UPDATE place SET place = $place, `from` = $from, `to` = $to, description = $description WHERE id = $id";
$result= mysql_query($sql);
if(!$result){
echo "Cannot.. failed";
}else{
//echo $sql;
// Show confirmation
echo "Your place is added to your database.<br>";
}
// Show link to index.html
echo "<br><a href = 'tryroute.php'>Go back to the page</a>";
?>
</body>
what is the problem with this???

Posted: Wed Jul 18, 2007 2:14 pm
by superdezign
Firstly, you have HUGE security holes (hint: mysql_real_escape_string).

Secondly, what is the mysql_error from the query?

Posted: Wed Jul 18, 2007 2:55 pm
by feyd
I would imagine it has something to do with $route, $from, $to and $description. Something tells me some of them aren't numbers.

Posted: Wed Jul 18, 2007 3:09 pm
by xiao_dolp
what you mean by HUGE security holes??

the error is
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 `route_id` = 55' at line 1
yup, only $id is int, other is all varchar.

Posted: Wed Jul 18, 2007 3:11 pm
by feyd
echo $sql.

Posted: Wed Jul 18, 2007 5:54 pm
by xiao_dolp
This is what it show when i echo $sql and mysql_error.
UPDATE place SET place = testing, `from` = testing, `to` = testing, description = , WHERE `id` = 55You 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 `id` = 55' at line 1

Posted: Wed Jul 18, 2007 8:33 pm
by feyd
Notice anything odd about your query?

Posted: Wed Jul 18, 2007 10:31 pm
by volka
string literals have to be quoted for mysql.

Code: Select all

UPDATE
  tablename
SET
  x=3, /* a number, no quotes needed */
  y=`z`, /* mysql assigns the value of the field z (of the same records), backticks optinal in this case */
  z='foo bar' /* mysql assigns the string foo bar to z, quotes are mandatory */