Apostrophe in PHP to SQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
supersonictt
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2011 7:08 pm

Apostrophe in PHP to SQL

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Apostrophe in PHP to SQL

Post 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.
supersonictt
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2011 7:08 pm

Re: Apostrophe in PHP to SQL

Post 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!!
temidayo
Forum Contributor
Posts: 109
Joined: Fri May 23, 2008 6:17 am
Location: Nigeria

Re: Apostrophe in PHP to SQL

Post by temidayo »

mysql_real_escape_string() like every other PHP-MySQL functions requires connection with
the database.
supersonictt
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2011 7:08 pm

Re: Apostrophe in PHP to SQL

Post 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.
supersonictt
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2011 7:08 pm

Re: Apostrophe in PHP to SQL

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Apostrophe in PHP to SQL

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Apostrophe in PHP to SQL

Post 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);
supersonictt
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2011 7:08 pm

Re: Apostrophe in PHP to SQL

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Apostrophe in PHP to SQL

Post 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.
supersonictt
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2011 7:08 pm

Re: Apostrophe in PHP to SQL

Post 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
supersonictt
Forum Newbie
Posts: 9
Joined: Mon Jul 11, 2011 7:08 pm

Re: Apostrophe in PHP to SQL

Post 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!
Post Reply