Jump to content


Photo

need some help with debugging, htmlentities an escapestrings.

htmlentities escapestring

  • Please log in to reply
11 replies to this topic

#1 Rollins

Rollins

    Newbie

  • Members
  • Pip
  • 36 posts
  • Gender:Male
  • Location:The Netherlands, Achterhoek
  • Interests:Persons...
    Sleeping...
    Internet...

Posted 07 April 2012 - 12:47 PM

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.

There are no Stupid Questions....just Stupid ppl who refuse to answer any.

my game is http://www.hyperiums.com. been playig it over 10 years and looking forward for 10 more...nice, clean, simple and a big international playerbase.

"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

Albert Einstein.


#2 birbal

birbal

    Devoted Member

  • Members
  • PipPipPipPipPip
  • 2,531 posts
  • Gender:Male
  • Location:india->kolkata
  • Interests:everything about computer and programming:specifically Web development and everything others which makes me interested
  • Languages:(x)html,css,(pl)sql,php,xml,xslt,xsd,javascript,java

Posted 07 April 2012 - 01:04 PM

what does $query print?
Tools: Mozilla tools || Mobile emulator ||
IDE: Netbeans 7.3 on Twilight theme (php) , Eclipse(java)
Debugger: Xdebug, Cachgrind
stop using mysql extension it is obsolete now. use mysqli instead or more better PDO for secure ,efficient database handle

#3 Rollins

Rollins

    Newbie

  • Members
  • Pip
  • 36 posts
  • Gender:Male
  • Location:The Netherlands, Achterhoek
  • Interests:Persons...
    Sleeping...
    Internet...

Posted 07 April 2012 - 02:09 PM

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

There are no Stupid Questions....just Stupid ppl who refuse to answer any.

my game is http://www.hyperiums.com. been playig it over 10 years and looking forward for 10 more...nice, clean, simple and a big international playerbase.

"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

Albert Einstein.


#4 Rollins

Rollins

    Newbie

  • Members
  • Pip
  • 36 posts
  • Gender:Male
  • Location:The Netherlands, Achterhoek
  • Interests:Persons...
    Sleeping...
    Internet...

Posted 07 April 2012 - 06:02 PM

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

There are no Stupid Questions....just Stupid ppl who refuse to answer any.

my game is http://www.hyperiums.com. been playig it over 10 years and looking forward for 10 more...nice, clean, simple and a big international playerbase.

"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

Albert Einstein.


#5 boen_robot

boen_robot

    XSLT senior

  • Moderator
  • PipPipPipPipPipPipPip
  • 8,486 posts
  • Gender:Male
  • Location:europe://Bulgaria/Plovdiv
  • Interests:Everything having to do with computers... and science.
  • Languages:(X)HTML, CSS, XML, XSLT, Schema, PHP, JavaScript (a little), other XML based...

Posted 07 April 2012 - 06:26 PM

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

The greatest difficulty in programming is not in finding answers, but in asking yourself the right questions. -- If nobody has said it before, then I'd like to take credit of thinking this up (during summer of 2010).
"Complex problems often have the simplest solutions" -- Not sure who said that first.
=== My projects (all feedback welcomed) ===
XML_XSLT2Processor(0.5.3) - perform XSLT 2.0 transformations in PHP.
PEAR2_Net_Transmitter(1.0.0a4) - reliable sockets.
PEAR2_Cache_SHM(0.1.2) - persistent data storage wrapper.
=== Useful tools ===
NetBeans - full featured PHP IDE, as well as a decent code editor for other things.
Fiddler2 - The best free HTTP debugger. Performance tuning, security check, integrity check, custom requests and more, all made easy.
Gobby - That's NOT my Nickname! Look at the topic.

#6 Rollins

Rollins

    Newbie

  • Members
  • Pip
  • 36 posts
  • Gender:Male
  • Location:The Netherlands, Achterhoek
  • Interests:Persons...
    Sleeping...
    Internet...

Posted 08 April 2012 - 08:58 AM

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.

There are no Stupid Questions....just Stupid ppl who refuse to answer any.

my game is http://www.hyperiums.com. been playig it over 10 years and looking forward for 10 more...nice, clean, simple and a big international playerbase.

"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

Albert Einstein.


#7 birbal

birbal

    Devoted Member

  • Members
  • PipPipPipPipPip
  • 2,531 posts
  • Gender:Male
  • Location:india->kolkata
  • Interests:everything about computer and programming:specifically Web development and everything others which makes me interested
  • Languages:(x)html,css,(pl)sql,php,xml,xslt,xsd,javascript,java

Posted 08 April 2012 - 10:49 AM

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.
Tools: Mozilla tools || Mobile emulator ||
IDE: Netbeans 7.3 on Twilight theme (php) , Eclipse(java)
Debugger: Xdebug, Cachgrind
stop using mysql extension it is obsolete now. use mysqli instead or more better PDO for secure ,efficient database handle

#8 Rollins

Rollins

    Newbie

  • Members
  • Pip
  • 36 posts
  • Gender:Male
  • Location:The Netherlands, Achterhoek
  • Interests:Persons...
    Sleeping...
    Internet...

Posted 13 April 2012 - 04:45 PM

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?

There are no Stupid Questions....just Stupid ppl who refuse to answer any.

my game is http://www.hyperiums.com. been playig it over 10 years and looking forward for 10 more...nice, clean, simple and a big international playerbase.

"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

Albert Einstein.


#9 birbal

birbal

    Devoted Member

  • Members
  • PipPipPipPipPip
  • 2,531 posts
  • Gender:Male
  • Location:india->kolkata
  • Interests:everything about computer and programming:specifically Web development and everything others which makes me interested
  • Languages:(x)html,css,(pl)sql,php,xml,xslt,xsd,javascript,java

Posted 13 April 2012 - 05:01 PM

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.

Tools: Mozilla tools || Mobile emulator ||
IDE: Netbeans 7.3 on Twilight theme (php) , Eclipse(java)
Debugger: Xdebug, Cachgrind
stop using mysql extension it is obsolete now. use mysqli instead or more better PDO for secure ,efficient database handle

#10 Rollins

Rollins

    Newbie

  • Members
  • Pip
  • 36 posts
  • Gender:Male
  • Location:The Netherlands, Achterhoek
  • Interests:Persons...
    Sleeping...
    Internet...

Posted 13 April 2012 - 05:22 PM

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?

There are no Stupid Questions....just Stupid ppl who refuse to answer any.

my game is http://www.hyperiums.com. been playig it over 10 years and looking forward for 10 more...nice, clean, simple and a big international playerbase.

"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

Albert Einstein.


#11 boen_robot

boen_robot

    XSLT senior

  • Moderator
  • PipPipPipPipPipPipPip
  • 8,486 posts
  • Gender:Male
  • Location:europe://Bulgaria/Plovdiv
  • Interests:Everything having to do with computers... and science.
  • Languages:(X)HTML, CSS, XML, XSLT, Schema, PHP, JavaScript (a little), other XML based...

Posted 13 April 2012 - 05:45 PM

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.
The greatest difficulty in programming is not in finding answers, but in asking yourself the right questions. -- If nobody has said it before, then I'd like to take credit of thinking this up (during summer of 2010).
"Complex problems often have the simplest solutions" -- Not sure who said that first.
=== My projects (all feedback welcomed) ===
XML_XSLT2Processor(0.5.3) - perform XSLT 2.0 transformations in PHP.
PEAR2_Net_Transmitter(1.0.0a4) - reliable sockets.
PEAR2_Cache_SHM(0.1.2) - persistent data storage wrapper.
=== Useful tools ===
NetBeans - full featured PHP IDE, as well as a decent code editor for other things.
Fiddler2 - The best free HTTP debugger. Performance tuning, security check, integrity check, custom requests and more, all made easy.
Gobby - That's NOT my Nickname! Look at the topic.

#12 Rollins

Rollins

    Newbie

  • Members
  • Pip
  • 36 posts
  • Gender:Male
  • Location:The Netherlands, Achterhoek
  • Interests:Persons...
    Sleeping...
    Internet...

Posted 13 April 2012 - 06:59 PM

that's cool stuff!

thanks for the rep!

There are no Stupid Questions....just Stupid ppl who refuse to answer any.

my game is http://www.hyperiums.com. been playig it over 10 years and looking forward for 10 more...nice, clean, simple and a big international playerbase.

"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

Albert Einstein.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users