Rollins Posted April 7, 2012 Share Posted April 7, 2012 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! Link to comment Share on other sites More sharing options...
birbal Posted April 7, 2012 Share Posted April 7, 2012 what does $query print? Link to comment Share on other sites More sharing options...
Rollins Posted April 7, 2012 Author Share Posted April 7, 2012 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 More sharing options...
Rollins Posted April 7, 2012 Author Share Posted April 7, 2012 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 More sharing options...
boen_robot Posted April 7, 2012 Share Posted April 7, 2012 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 More sharing options...
Rollins Posted April 8, 2012 Author Share Posted April 8, 2012 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.. Link to comment Share on other sites More sharing options...
birbal Posted April 8, 2012 Share Posted April 8, 2012 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 More sharing options...
Rollins Posted April 13, 2012 Author Share Posted April 13, 2012 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 More sharing options...
birbal Posted April 13, 2012 Share Posted April 13, 2012 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. Link to comment Share on other sites More sharing options...
Rollins Posted April 13, 2012 Author Share Posted April 13, 2012 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 More sharing options...
boen_robot Posted April 13, 2012 Share Posted April 13, 2012 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 More sharing options...
Rollins Posted April 13, 2012 Author Share Posted April 13, 2012 that's cool stuff! thanks for the rep! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.