@ issue (seeking experience before i blindly proceed)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

@ issue (seeking experience before i blindly proceed)

Post 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 @)
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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?
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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);
}
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

little trick

Post 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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Glad you got it fixed.
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post by m3rajk »

no problem. turned out it was exactly as you originally said. i just happened to have a freak incident with timing
Post Reply