Page 1 of 1

Apostrophe in PHP to SQL

Posted: Mon Jul 11, 2011 7:14 pm
by supersonictt
Hello,

I am new to sql and php, as I am using a wysiwyg software to build up my site.
I have a sign up form that is connected properly to a mysql database, and in phpmyadmin I can see the data when someone uses the signup form. The only problem is when someone puts in Full Name a name like:
Ala'
Sa'ad
Ra'ed
the sign up is successful and a confirmation email is sent to that person BUT no record is there in mysql database! I searced here and there and found out that I can insert MYSQL_REAL_ESCAPE_STRING, but as I said, I am really not good in php and sql, I always try things with trial and error, but this time it didn't work for me

Here is the code for the page:

Code: Select all

<?php
$error_message = "";
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
   $action = isset($_POST['action']) ? $_POST['action'] : '';
   $mysql_server = 'localhost';
   $mysql_username = 'username';
   $mysql_password = '********';
   $mysql_database = 'database';
   $mysql_table = 'members';

   $success_page = 'http://www.site.com/success.html';

   if ($action == 'signup')
   {
      $newusername = $_POST['username'];
      $newemail = $_POST['email'];
      $newpassword = $_POST['password'];
      $confirmpassword = $_POST['confirmpassword'];
      $newfullname = $_POST['fullname'];
      $birthday = $_POST['birthday'];
      if ($newpassword != $confirmpassword)
      {
         $error_message = 'Password and Confirm Password are not the same!';
      }
      else
      if (!ereg("^[A-Za-z0-9_!@$.-]{1,50}$", $newusername))
      {
         $error_message = 'Username is not valid, please check and try again!';
      }
      else
      if (!ereg("^[A-Za-z0-9_!@$]{1,50}$", $newpassword))
      {
         $error_message = 'Password is not valid, please check and try again!';
      }
      else
      if (!ereg("^[A-Za-z0-9_!@$.' &-]{1,50}$", $newfullname))
      {
         $error_message = 'Full name is not valid, please check and try again!';
      }
      else
      if (!ereg("^[-A-Za-z0-9_]+[-A-Za-z0-9_.]*[@]{1}[-A-Za-z0-9_]+[-A-Za-z0-9_.]*[.]{1}[A-Za-z]{2,5}$", $newemail))
      {
         $error_message = 'Email is not a valid email address. Please check and try again.';
      }
      if (empty($error_message))
      {
         $db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
         mysql_select_db($mysql_database, $db);
         $sql = "SELECT username FROM ".$mysql_table." WHERE username = '".$newusername."'";
         $result = mysql_query($sql, $db);
         if ($data = mysql_fetch_array($result))
         {
            $error_message = 'Username is already taken. Please choose another username.';
         }
      }
      if (empty($error_message))
      {
         $crypt_pass = md5($newpassword);
         $sql = "INSERT `".$mysql_table."` (`username`, `password`, `fullname`, `email`, `birthday`, `active`) VALUES ('$newusername', '$crypt_pass', '$newfullname', '$newemail', '$birthday', 0)";
         $result = mysql_query($sql, $db);
         mysql_close($db);

As you can see, in fullname validation, I allowed the apostrophe to be used, so that's why the registration returns "successful", and I know this record doesn't appear in sql table as the apostrophe interrupts the field from being recorded.

So please, where do I put the MYSQL_REAL_ESCAPE_STRING? And in what format?
If someone could please rewrite it (the part where it should be) I would be very thankful.

Thank you.

Re: Apostrophe in PHP to SQL

Posted: Mon Jul 11, 2011 7:52 pm
by califdon
$newfullname = mysql_real_escape_string($_POST['fullname']);

Actually, you should use mysql_real_escape_string() on ALL your alphabetic inputs, to protect against "sql injection", which is an exploit that can be used to inject malicious code from a web form into your system. If you're going to be doing this on a public website, you need to read about sql injection. Google it.

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 3:24 am
by supersonictt
califdon wrote:$newfullname = mysql_real_escape_string($_POST['fullname']);

Actually, you should use mysql_real_escape_string() on ALL your alphabetic inputs, to protect against "sql injection", which is an exploit that can be used to inject malicious code from a web form into your system. If you're going to be doing this on a public website, you need to read about sql injection. Google it.
Thanks a lot for your reply.

Actually I tried this before, but after submitting the form it gives me this error:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'racketre'@'localhost' (using password: NO) in /home/racketre/public_html/test.php on line 21
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/racketre/public_html/test.php on line 21

I put the string you mentioned as it is on line 21, and it is not working!! Does it require sql connection? As I read somewhere that there should be a connection to the database before the real escape function.
Well, I also tried the known string for connection with database but it didn't work as well!!

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 3:35 am
by temidayo
mysql_real_escape_string() like every other PHP-MySQL functions requires connection with
the database.

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 3:38 am
by supersonictt
OK Wait, it is PARTIALLY solved :)

What I did is this:

I added:
$link = mysql_connect('mysql_host', 'mysql_username', 'mysql_password')
OR die(mysql_error());

and:
$newfullname = mysql_real_escape_string($_POST['fullname']);

and I added a backslash just before the apostrophe in the fullname validation:
if (!ereg("^[A-Za-z0-9_!@$\'. &-]{1,50}$", $newfullname))

Now, the data are recorded PROPERLY in mysql database, but the confirmation email that is sent upon successful registration is like this:
Full Name: Ala\'a (as an example) it should be: Ala'a

Is there a way to solve that?

Thank you.

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 4:08 am
by supersonictt
supersonictt wrote:OK Wait, it is PARTIALLY solved :)

What I did is this:

I added:
$link = mysql_connect('mysql_host', 'mysql_username', 'mysql_password')
OR die(mysql_error());

and:
$newfullname = mysql_real_escape_string($_POST['fullname']);

and I added a backslash just before the apostrophe in the fullname validation:
if (!ereg("^[A-Za-z0-9_!@$\'. &-]{1,50}$", $newfullname))

Now, the data are recorded PROPERLY in mysql database, but the confirmation email that is sent upon successful registration is like this:
Full Name: Ala\'a (as an example) it should be: Ala'a

Is there a way to solve that?

Thank you.
Sorry guys! I told you before that with trial and error I solve things :)
Now the confirmation email is sent properly:
Full Name: Ala'a

I added stripslashes string:
$message .= stripslashes($newfullname);

And now, everything is working perfectly.

As califdon said in his post above, he advised me to add the escape function in ALL alphabetic inputs, well, that means to be added in:
$newfullname
$newusername

or to all of them?

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 12:38 pm
by califdon
All of them. The reason is that without escaping characters like quotation marks that might potentially have such characters entered by the user, a hostile user could enter something like:
'; echo "DROP TABLE yourtable";
When this is substituted in your SQL string, the apostrophe is likely to terminate the SQL string and the semicolon would end the PHP statement, then the following might be interpreted as a valid PHP command. If the user could guess your table name, they could delete your table! By inserting the backslash escape character, that won't happen.

This doesn't eliminate EVERY possibility for malicious input, but it greatly reduces your exposure. Do read about it.

Congratulations on figuring out the stripslashes(). Also, having seen that you had a MySQL connection earlier in your script, I skipped mentioning that it is, indeed, a MySQL function and only works after a connection has been established.

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 2:58 pm
by Weirdan
califdon wrote:$newfullname = mysql_real_escape_string($_POST['fullname']);
That wasn't, in fact, a good advice. When you apply escaping you need to consider where you do that. In this case you're escaping prior to processing, allowing further code to fiddle with escaped string, which could cause that code to fail (because it expects original unmodified string), but also could cause value to become invalid (if that code, for example, sanitizes it in some way). Ideally escaping should be applied where the query (or query chunk, if query building is a complex multistep process) is built, like this:

Code: Select all

// get pristine data (if you have magic quotes on their effect should be undone before)
$fullname = $_POST['fullname'];
// sanitize (or validate) data. We're sanitizing the data in this case.
$fullname = preg_replace('[^A-Za-z\'-]', '', $fullname);
// use it in the query
$sql = "
   insert into some_table (fullname)
   values ('" . mysql_real_escape_string($fullname, $con) . "')
";
mysql_query($sql, $con);
Of course it's a bit too verbose with mysql_real_escape_string() , it becomes more readable when you use something like PDO:

Code: Select all

$sql = "
   insert into some_table (fullname)
   values ({$con->quote($fullname)})
";
$con->query($sql);

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 3:20 pm
by supersonictt
Weirdan wrote:
califdon wrote:$newfullname = mysql_real_escape_string($_POST['fullname']);
That wasn't, in fact, a good advice. When you apply escaping you need to consider where you do that. In this case you're escaping prior to processing, allowing further code to fiddle with escaped string, which could cause that code to fail (because it expects original unmodified string), but also could cause value to become invalid (if that code, for example, sanitizes it in some way). Ideally escaping should be applied where the query (or query chunk, if query building is a complex multistep process) is built, like this:

Code: Select all

// get pristine data (if you have magic quotes on their effect should be undone before)
$fullname = $_POST['fullname'];
// sanitize (or validate) data. We're sanitizing the data in this case.
$fullname = preg_replace('[^A-Za-z\'-]', '', $fullname);
// use it in the query
$sql = "
   insert into some_table (fullname)
   values ('" . mysql_real_escape_string($fullname, $con) . "')
";
mysql_query($sql, $con);
Of course it's a bit too verbose with mysql_real_escape_string() , it becomes more readable when you use something like PDO:

Code: Select all

$sql = "
   insert into some_table (fullname)
   values ({$con->quote($fullname)})
";
$con->query($sql);
Thank you for replying and mentioning some stuff.

Well, do you mean there is a better thing to do in my case other than what I mentioned?

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 4:14 pm
by Weirdan
Well, do you mean there is a better thing to do in my case other than what I mentioned?
Yes. All those nasty workarounds you had to implement (allowing backslashes in names, applying stripslashes() on the data that was going in the email) wouldn't be required if you applied escaping in the right place (where the query is built). Read my post above, paying attention to where you need to apply escaping.

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 5:18 pm
by supersonictt
So basically I do this (in my case):

under:
$newfullname = $_POST['fullname'];
I add:
$newfullname = preg_replace('[^A-Za-z\'-]', '', $newfullname);

Then, I modify this:
$sql = "INSERT `".$mysql_table."` (`username`, `password`, `fullname`, `email`, `birthday`, `active`) VALUES ('$newusername', '$crypt_pass', '$newfullname', '$newemail', '$birthday', 0)";
to become:
$sql = "INSERT `".$mysql_table."` (`username`, `password`, `fullname`, `email`, `birthday`, `active`) VALUES ('$newusername', '$crypt_pass', mysql_real_escape_string($newfullname, $con), '$newemail', '$birthday', 0)";

and then, I modify this:
$result = mysql_query($sql, $db);
to this:
$result = mysql_query($sql, $con);

Is that all? Or shall I also use the second syntax in your post?

Thanks a lot

Re: Apostrophe in PHP to SQL

Posted: Tue Jul 12, 2011 5:51 pm
by supersonictt
Oh man forget my last post!!!

The problem is that I really know nothing about php and sql! So I cannot figure out how to apply what you mentioned in your post without trial and error!
I wish I had some knowledge about these stuff, I guess it would be easy for me to do that!

Thanks a lot for your help :) and please don't bother yourself helping me :) you don't need to re-write the whole code for me!