11 replies to this topic

[Rollins]

hey all!

can someone please give me some advice on debugging and security matters.......

i am working on a script to insert data ina DB and it got me stuck on a error wich i think is being caused by the escape string or the $sql query. tried changing the order of events....without the escapestring.....and some random things but it keeps giving me:

ERROR: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(dt, username, email, pwd)values('Saturday 7 April 2012 14:11:42', '1', '2', '8'' at line 1

the code that's bugging me.....

//request data from form(method=post)
$username = htmlentities($_REQUEST['username']);
$email = htmlentities($_REQUEST['email']);
$password = htmlentities($_REQUEST['password']);
$datetime = date("l j F Y H:i:s"); //create date time
 
//connect db
mysql_connect("$db_host", "$db_username", "$db_password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
 
//clean data
$res_username = mysql_real_escape_string ($username);
$res_email = mysql_real_escape_string ($email);
$res_password = mysql_real_escape_string ($password);
 
if (empty ($res_username) or empty($res_email) or empty($res_password) or empty($datetime)){
		  die ('error!');
		  }
 
//insert data
$sql="insert into $tbl_name(dt, username, email, pwd)values('$datetime', '$res_username', '$res_email', '$res_password')";
$result=mysql_query($sql);
if (!$result){
  die('ERROR: ' . mysql_errno()  .mysql_error());
}
 
mysql_close();

what's going wrong here?

can someone give me advice on how to deal with issues like this?

is this a good way to add db security? do i need to request htmlentities AND a mysql escape string?

thanks Roll!

Edited by Rollins, 07 April 2012 - 12:52 PM.

[birbal]

what does $query print?

[Rollins]

you mean the $sql string where i insert the date?

insert into (dt, username, email, pwd)values('Saturday 7 April 2012 15:58:58', '1', '2', '8')

[Rollins]

ERROR: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(dt, username, email, pwd)values('Saturday 7 April 2012 14:11:42', '1', '2', '8'' at line 1

.....should the end not be '8')' ?

[boen_robot]

I think there should be spaces around the word "values" and the field list, i.e.

$sql="insert into $tbl_name (dt, username, email, pwd) values ('$datetime', '$res_username', '$res_email', '$res_password')";

[Rollins]

i wish it was that simple.....tried spacing it!

actually it really was a simple thing......

$sql="INSERT INTO $tbl_name(username, password, email, datetime) VALUES ('$res_username', '$res_password', '$res_email', '$datetime')";

$sql="INSERT INTO $table_name(username, password, email, datetime) VALUES ('$res_username', '$res_password', '$res_email', '$datetime')";

i screwed up the table name....sorry to waste your time.

i'll be looking out for a debug checklist.
.

Edited by Rollins, 08 April 2012 - 09:29 AM.

[birbal]

Rollins, on 07 April 2012 - 02:09 PM, said:

you mean the $sql string where i insert the date?

insert into (dt, username, email, pwd)values('Saturday 7 April 2012 15:58:58', '1', '2', '8')

set your php.ini error level to E_ALL and display error is enabled.
so that you would catch it easily here that tablename is missing and is not geting evaluated.

[Rollins]

thanks for your advice....ill see what i can find on that how to use it.

something els has got my attention now.... the data from the form is is sanitized by the htmlentities function first and then by mysql_real_escape_string ().

now when a data is passed with slashes and ampersands it does not do what i expected it would do and that is alter the data.... no \ is added to the ' for instance

should the data go through the escape string first and then strip the html entities?

[birbal]

mysql_real_escape_string() will be the always last one which will you use before using it in query.

escaping is for the chafraters which have speical meanings to database. so it is just escaped when it executed by database engine. once it is executed it will be the same character. it does not change the value at last. once the query executed there is no meaning of escaping.thus you cant see the escapiing characters in database.

Edited by birbal, 13 April 2012 - 05:02 PM.

[Rollins]

aaah oke?

so.....

$res_username = mysql_real_escape_string ($username);

does not send '$username' but it sends the entire 'mysql_real_escape_string ($username)' to the DB wich filters the data to save data to insert?

[boen_robot]

Neither.

mysql_real_escape_string() takes $username, and turns it into something which can be places safely between apostrophes in an SQL query. This something is, in this case, stored in $res_username.


Suppose $username contained an apostrophe, like "O'Reilly". If you just place that in an SQL query, like:

"INSERT INTO users (username) VALUES ('$username')"

the result would be

INSERT INTO users (username) VALUES ('O'Reilly')

which results in a syntax error.

mysql_real_escape_string() handles this character, among others, by "escaping" it, so that

"INSERT INTO users (username) VALUES ('$res_username')"

would look like

INSERT INTO users (username) VALUES ('O\'Reilly')

which is a valid SQL query. One which results in "O'Reilly" being inserted into the DB.

[Rollins]

that's cool stuff!

thanks for the rep!