[RESOLVED] Inserting Data to mysql

kpowning

I have been recently browsing this site and many people are saying to use the
(mysql_real_escape_string) before inserting, updating or running almost any mysql input code. Is this necessary?

I'm lost 🙁

Below is a sample code I use for new users registration. Let me know what you think and will this work if say the user has a username of (josh 1) which includes a space? or even (josh_1)? I just want to make sure im doing everything correctly. Thanks to all in advance. MYSQL INJECTION = CONFUSED CODER HERE.


////////////////////////////////////////////////////
///// Insert New Member Username & Password ////////
////////////////////////////////////////////////////
$pass = md5($_SESSION['password']);
$douser = "INSERT INTO user (username, password, lastactivity, regdate, ipaddress, trial) VALUES ('".$_SESSION['username']."', '$pass', now(), now(), '".$_SESSION['ipaddress']."', 'In Process')";
$insertnewuser = mysql_query($douser, $erotic) or die(mysql_error());
////////////////////////////////////////////////////

laserlight

I have been recently browsing this site and many people are saying to use the
(mysql_real_escape_string) before inserting, updating or running almost any mysql input code. Is this necessary?

Yes, it is necessary for storing string data.

Below is a sample code I use for new users registration. Let me know what you think and will this work if say the user has a username of (josh 1) which includes a space? or even (josh_1)? I just want to make sure im doing everything correctly. Thanks to all in advance. MYSQL INJECTION = CONFUSED CODER HERE.

Sure, it will work correctly if the user has a username with spaces and underscores. However, it will not work correctly if the user has a username with single quotes.

kpowning

Thank you.

would this be considered correct?

$pass = md5($_SESSION['password']);
$douser = sprintf("INSERT INTO user (username, password, lastactivity, regdate, ipaddress, trial) VALUES ('%s', %d, %d, %d, '%s', %d)",
                    mysql_real_escape_string($_SESSION['username']), 
                    $pass,
                    now(),
                    now(),
                    mysql_real_escape_string($_SESSION['ipaddress']),
                    'In Process');

$insertnewuser = mysql_query($douser, $erotic) or die(mysql_error());

kpowning

And your saying do it for all like this?

$memberinfo = sprintf("INSERT INTO userinfo (name, phone, displayphone, phone2, displayphone2, email, displayemail,
                address, city, state, zip, haircolor, eyecolor, weight, height, bustsize, waistsize,
				ethnicity, sexualpref, tatoos, bodypiercings, gender, available, callstatus, rate,
			    daytime, otherrates, website, travel, age, languages, interests, hobbies, adtype,
				multiplegirls, category1, visitingduration, arrivedate, departdate, jobearnings, jobinfo,
				category2, featured, catchphrase, bio, worktype, agencyname, addimages, msnhandle,
				aolhandle, yahoohandle, termsagreement, status) 
				VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
				        '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
						'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
						'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
						'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
						'%s', %d, %d)",
					    mysql_real_escape_string($_SESSION['name'], $erotic),
						mysql_real_escape_string($_SESSION['phone'], $erotic),
						mysql_real_escape_string($_SESSION['displayphone'], $erotic),
						mysql_real_escape_string($_SESSION['phone2'], $erotic),
						mysql_real_escape_string($_SESSION['displayphone2'], $erotic),
						mysql_real_escape_string($_SESSION['email'], $erotic),
						mysql_real_escape_string($_SESSION['displayemail'], $erotic),
						mysql_real_escape_string($_SESSION['address'], $erotic),
						mysql_real_escape_string($_SESSION['city'], $erotic),
						mysql_real_escape_string($_SESSION['state'], $erotic),
						mysql_real_escape_string($_SESSION['zip'], $erotic),
						mysql_real_escape_string($_SESSION['haircolor'], $erotic),
						mysql_real_escape_string($_SESSION['eyecolor'], $erotic),
						mysql_real_escape_string($_SESSION['weight'], $erotic),
						mysql_real_escape_string($_SESSION['height'], $erotic),
						mysql_real_escape_string($_SESSION['bustsize'], $erotic),
						mysql_real_escape_string($_SESSION['waistsize'], $erotic),
						mysql_real_escape_string($_SESSION['ethnicity'], $erotic),
						mysql_real_escape_string($_SESSION['sexualpreference'], $erotic),
						mysql_real_escape_string($_SESSION['tattoos'], $erotic),
						mysql_real_escape_string($_SESSION['bodypiercings'], $erotic),
						mysql_real_escape_string($_SESSION['gender'], $erotic),
						mysql_real_escape_string($_SESSION['available'], $erotic),
						mysql_real_escape_string($_SESSION['callstatus'], $erotic),
						mysql_real_escape_string($_SESSION['rates'], $erotic),
						mysql_real_escape_string($_SESSION['daytime'], $erotic),
						mysql_real_escape_string($_SESSION['otherrates'], $erotic),
						mysql_real_escape_string($_SESSION['websiteaddress'], $erotic),
						mysql_real_escape_string($_SESSION['travel'], $erotic),
						mysql_real_escape_string($_SESSION['age'], $erotic),
						mysql_real_escape_string($_SESSION['languages'], $erotic),
						mysql_real_escape_string($_SESSION['interested'], $erotic),
						mysql_real_escape_string($_SESSION['hobbies'], $erotic),
						mysql_real_escape_string($_SESSION['adtype'], $erotic),
						mysql_real_escape_string($_SESSION['multiplegirls'], $erotic),
						mysql_real_escape_string($_SESSION['category1'], $erotic),
						mysql_real_escape_string($_SESSION['visitingduration'], $erotic),
						mysql_real_escape_string($_SESSION['arrivedate'], $erotic),
						mysql_real_escape_string($_SESSION['departdate'], $erotic),
						mysql_real_escape_string($_SESSION['jobearnings'], $erotic),
						mysql_real_escape_string($_SESSION['jobinfo'], $erotic),
						mysql_real_escape_string($_SESSION['category2'], $erotic),
						mysql_real_escape_string($_SESSION['featured'], $erotic),
						mysql_real_escape_string($_SESSION['catchphrase'], $erotic),
						mysql_real_escape_string($_SESSION['bio'], $erotic),
						mysql_real_escape_string($_SESSION['worktype'], $erotic),
						mysql_real_escape_string($_SESSION['agencyname'], $erotic),
						mysql_real_escape_string($_SESSION['additionalimages'], $erotic),
						mysql_real_escape_string($_SESSION['msnemail'], $erotic),
						mysql_real_escape_string($_SESSION['aolscreenname'], $erotic),
						mysql_real_escape_string($_SESSION['yahoohandle'], $erotic),
						'Agree',
						'Pending');

mysql_query($memberinfo, $erotic) or die(mysql_error());

If this is the case how do you perform an UPDATE query this way and is there a way to optimize this?

THANKS FOR ALL YOUR HELP!

laserlight

And your saying do it for all like this?

Yes.

If this is the case how do you perform an UPDATE query this way

Roughly the same, except you now have SET column_x='%s'.

is there a way to optimize this?

Use prepared statements with the MySQLi or PDO extensions.

kpowning

can you show me a full small UPDATE example? PLEASE ?

laserlight

$query = sprintf("UPDATE Users SET address='%s', email='%s' WHERE id=%d",
                 mysql_real_escape_string($_POST['address']),
                 mysql_real_escape_string($_POST['email']),
                 $_SESSION['user_id']);
mysql_query($query);

kpowning

Thank you for all your help. So, basically I shouldn't have to worry anymore as long as I do all my queries this way?

Thanks a million... 🙂

laserlight

So, basically I shouldn't have to worry anymore as long as I do all my queries this way?

Yes, at least concerning SQL injection. Of course, it is still possible that there are loopholes in mysql_real_escape_string() itself, but it is pointless worrying about that.

kpowning

How do you incorporate this correctly?

$query = sprintf("UPDATE Users SET address='%s', email='%s' WHERE id=%d",
                 mysql_real_escape_string($_POST['address']),
                 mysql_real_escape_string($_POST['email']),
                 'Mark'); // specify a default value
mysql_query($query);

Basically trying to specify a couple defauly values. Due you use '%s' or %d to do this and the value for instance 'Mark' shown above? Thanks

laserlight

Basically trying to specify a couple defauly values. Due you use '%s' or %d to do this and the value for instance 'Mark' shown above?

Specify default values in the database. If you want to specify them in the SQL statement, then you might as well embed them into the statement. For more information on the format specifiers %s and %d, read the PHP manual on [man]sprintf/man.