Jump to content

need some help with debugging, htmlentities an escapestrings.


Rollins

Recommended Posts

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 dbmysql_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
Link to comment
Share on other sites

what does $query print?

Link to comment
Share on other sites

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')

Link to comment
Share on other sites

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')' ?

Link to comment
Share on other sites

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')";

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...