Page 1 of 1

@ issue (seeking experience before i blindly proceed)

Posted: Thu Aug 21, 2003 2:08 pm
by m3rajk
the debugging info returned when i added a few lines because nothing was being inserted:
debug: insert to user: INSERT INTO users (username, password, email, last_login_ip, last_login_date, enroll, gender) VALUES (Neo, 9cc9e1f6a22e05307d6831087ef5d1e6, edited_out@hotmail.com, 192.168.1.100, 2003-08-21 19:06:06, 2003-08-21 19:06:06, M)
1064
You have an error in your SQL syntax near '@hotmail.com, 192.168.1.100, 2003-08-21 19:06:06, 2003-08-21 19:06:06, M)' at line 1


edited_out@hotmail.com
i edited out my e-mail address. nothing else.

the last line is the return from mysql_escape_string() on the variable containing the e-mail address. in the input it is put through a funtion that uses the php addslashes($variable) before returning it to set it for the db.

obviously it needs to not be in the form of @. i'm wondering (since i can't find anything online or in the oreilly book that is specifically on this. they all talk about escapes in general) if \@ would work.

(in the mean time i'm going to look up the ascii value of @)

Posted: Thu Aug 21, 2003 6:00 pm
by Coco
i cant say ive had this problem myself
but then i always enclose strings like emails in double quotes
have you tried this?

Posted: Fri Aug 22, 2003 12:00 pm
by m3rajk
no. i used singlequotes. i used double quoted for the ENTIRE input string so i coule put the vatiables in


note: i'm making a forums for this site and i need to know so i can handle the random text people put in

Posted: Fri Aug 22, 2003 12:42 pm
by McGruff
INSERT INTO users (username, password, email, last_login_ip, last_login_date, enroll, gender) VALUES ('Neo', '9cc9e1f6a22e05307d6831087ef5d1e6', 'edited_out@hotmail.com', '192.168.1.100', '2003-08-21 19:06:06', '2003-08-21 19:06:06', 'M')

Enclosing column values in single quotes will fix it. You should always do this in any case for security reasons: this (and properly escaping strings) prevents query hijacking.

Posted: Fri Aug 22, 2003 9:17 pm
by fractalvibes
PHP has some pretty nice ways of escaping charactors also. Often when inserting values into a database, you can surround text fields with
\"SomeTextFieldFromForm\" within an sqlstring that is itself within double quotes.... I can only dream of this ease in my 8-hour a day ASP world....
As always, do what you must to safeguard against SQL Injection as was alluded to...thinking http://www.4guysfromrolla.com had a good article on this,
an asp site, but the principles apply regardless of the language.

fv

Posted: Sat Aug 23, 2003 6:00 pm
by m3rajk
rechicking, the only time it'snot in single quotes is in the insert statements. i'll fix that and get back to you

Posted: Wed Aug 27, 2003 8:03 pm
by m3rajk
getting back to everyone.....
debug: insert to user: INSERT INTO users (username, password, email, last_login_ip, last_login_date, enroll, gender) VALUES ('Neo', '9cc9e1f6a22e05307d6831087ef5d1e6', 'Archimedes5739@hotmail.com', '192.168.1.100', '2003-08-27 19:11:09', '2003-08-27 19:11:09', 'M')
1064
You have an error in your SQL syntax near '@hotmail.com, 192.168.1.100, 2003-08-27 19:11:09, 2003-08-27 19:11:09, M)' at line 1
the code causing the issue:

Code: Select all

function s2dbadd($db, $badaim, $badicq, $badmsn, $badyim){ # step 2 db additions
  include("/home/joshua/includes/fyd.altincs.php"); # includes file (precautionary measure)
  # step 2 non-passed variables
  $un=clean($_POST['un']); $pw=MD5($_POST['pw']); $email=clean($_POST['email']);  $dobm=clean($_POST['month']); $dobd=clean($_POST['day']); $doby=clean($_POST['year']); $gender=clean($_POST['gender']); $sexpref=clean($_POST['sexpref']); $marstat=clean($_POST['marstat']); $country=clean($_POST['country']); $feet=clean($_POST['feet']); $inches=clean($_POST['inches']); $waist=clean($_POST['waist']); $eye=clean($_POST['eye']); $hair=clean($_POST['hair']); $weight=clean($_POST['weight']); $body=clean($_POST['body']); $education=clean($_POST['education']); $employment=clean($_POST['employment']); $religion=clean($_POST['religion']); $ethnicity=clean($_POST['ethnicity']); $city=clean($_POST['city']); $spt=clean($_POST['spt']); $selfcat=clean($_POST['selfcat']); $aim=clean($_POST['aim']); $icq=clean($_POST['icq']); $mirc=clean($_POST['mirc']); $msn=clean($_POST['msn']); $yim=clean($_POST['yim']); # || date of birth month/day/year, gender, sexual preference, marital status, country, height (feet/inches), waist, eye, hair, weight, body type, education level, employment status, religion, ethnicity, city, state/province/territory, selfcat || aim, icq, mirc, msn, yim
  $llip=$_SERVER['REMOTE_ADDR']; $aff='Regular';# variables not in post but needed
  $lld=gmdate("Y-m-d H:i:s", time()); # get the GMT date/time based on time() (which returns the GMT timestamp)
  $enroll=gmdate("Y-m-d H:i:s", time()); # get the GMT date/time based on time() (which returns the GMT timestamp)
  
  # set various elements in prep to enter into the db
  if(isset($aim)&&$badaim){ $aim=NULL; } # set aim to a NULL entry if needed
  if(isset($icq)&&$badicq){ $icq=NULL; } # set icq to a NULL entry if needed
  if(isset($msn)&&$badmsn){ $msn=NULL; } # set msn to a NULL entry if needed
  if(isset($yim)&&$badyim){ $yim=NULL; } # set yim to a NULL entry if needed
  $height=(12*$feet)+$inches; # set height for storage
  $dob=$doby.'-'.$dobm.'-'.$dobd; # set DOB
  
  # make primary user table entry, retrieve uid
  mysql_query("INSERT INTO users (username, password, email, last_login_ip, last_login_date, enroll, gender) VALUES ('$un', '$pw', '$email', '$llip', '$lld', '$enroll', '$gender')", $db); # sets the main user table
  $query="INSERT INTO users (username, password, email, last_login_ip, last_login_date, enroll, gender) VALUES ('$un', '$pw', '$email', '$llip', '$lld', '$enroll', '$gender')";
  $errno=mysql_errno($db); $err=mysql_error($db); echo "<p>debug: insert to user: $query<br />$errno <br />$err</p>";
  $getuid=mysql_query("SELECT uid FROM users WHERE username='$un'", $db); # query for uid
  $uid=mysql_fetch_array($getuid); # set the uid
  # make stats table entry
  mysql_query("INSERT INTO stats (uid, username, dob, affiliation, sex_pref, mar_stat, country, height, waist, eye_color, hair_color, weight, body_type, education, employment, religion, ethnic, city, spt, self_cat, aim, icq, mirc, msn, yim) VALUES ($uid, $username, $dob, $aff, $sexpref, $marstat, $country, $height, $waist, $eye, $hair, $weight, $body, $education, $employment, $religion, $ethnicity, $city, $spt, $selfcat, $aim, $icq, $mirc, $msn, $yim)", $db); #insert stats into stats table
  # set uid into a cookie
  $expire=time()+60*60; # set expiration an hour from now
  setcookie(uid, md5($uid), $expire);
}

Posted: Thu Aug 28, 2003 3:31 am
by JayBird
take a look at these

Does the @ symbol require escape - http://forums.devshed.com/t67552/s.html


MySQL Strings - http://www.mysql.com/doc/en/String_syntax.html



IF THERE IS ONE IN THE ACTUAL EMAIL ADDRESS, TRY ESCAPING THE UNDERSCORE CHARACTER BECUASE THAT IS INTERPRETED AS A WILDCARD


Mark

little trick

Posted: Thu Aug 28, 2003 2:07 pm
by phpScott
I found a little trick that seems to work well. I haven't found any reason not to yet but let me know if there is.
Here is the trick

In input fields on the html form where there might be single of double quotes in the input I use the back tick ` that way in the the values don't get chopped in there happens to be a corponding quote in your sql.

php also has a handy function called addslashes() that will escape all the common special characters to avoid the quote issue.
Then when you retrieve the data you use stripslashes() to remove all the extra escaping. Be sure to use stripslashes in conjuction with addslashes or you will find your db turning into a pile of \\\\\\\ rubish.

phpScott

Posted: Thu Aug 28, 2003 8:17 pm
by m3rajk
i found out that it turned out that the save didn't take for some reason. it was using theold versoin.

getting out of xemacs and thentrying fixed it. i opened it back up andit was escaped, yet the cat right before hand had it unescaped, so it didn't save it for some reason even though i did hit "ctrl-x,s"


a friend of mine said he thinks i had the command interrupted by an auto-save. it's the only thing he can think of that'd cause it... i think it's was a combo between the 8 year old keybaods that seems to lose keys sporadically anyway, and the fact the processor is a p1 (pre-mmx)/166 that trhe hd read light went red just as i hit the save commands and i know my mom was sending ppl links to some pictures hosted on the server earlier (i love xeindows.. now i just need to get my rents to pay to update the cpu/motherboard/ram so it can work with how my mother's decided to suddenly start using the server : to show off images)

Posted: Thu Aug 28, 2003 9:43 pm
by McGruff
Glad you got it fixed.

Posted: Fri Aug 29, 2003 11:21 am
by m3rajk
no problem. turned out it was exactly as you originally said. i just happened to have a freak incident with timing