Mysqli_real_escape_string()

PHP
1

Hey guys!

I am trying to learn and practice how to use mysqli_real_escape_string() before using prepared statement… How would I do this for the following lines?Do I need to use mysqli for these lines?

I am trying to assign a variable to the following:

<?php
   
   if (!isset($_POST['submit'])) {
      header("Location: ../signup.php");
      exit();
   } else {
   	   include_once 'dbh.php';

   	   $first = mysqli_real_escape_string($conn, $_POST['first']);
   	   $last = mysqli_real_escape_string($conn, $_POST['last']);
   	   $email =mysqli_real_escape_string($conn, $_POST['email']) ;
   	   $uid = mysqli_real_escape_string($conn, $_POST['uid']);
   	   $password = mysqli_real_escape_string($conn, $_POST['pwd']);
   	   $user_permission = 1;
   	   $user_access = 1;
   	   $user_activate = 0;

   	   //Error handlers...

   	   if (empty($first) || empty($last) || empty($email) || empty($uid)|| empty($password)) {
   	   	   header("Location: ../signup.php?signup=empty");
   	   	   exit();
   	   } else {
          //Check if input characters are valid
   	   	    if (!preg_match("/^[a-zA-Z]*$/", $first) || !preg_match("/^[a-zA-Z]*$/", $last)) {
                header("Location: ../signup.php?signup=invalid");
                exit();
   	   	    } else {
   	   	    	
   	   	    	//Checking for valid emails
                 if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                     header("Location: ../signup.php?signup=email");
                     exit();
                 } else {
                   if (Strlen($password) < 5) {
                   header("Location: ../signup.php?signup=invvalidlength");
                   exit();
                   } else {
                   if (!preg_match("/^[a-zA-Z0-9]*$/", $password)){
                   	   header("Location: ../signup.php?signup=invvalidalphanumeric");
                   	   exit();
                 	 } else {
                      $sql = "SELECT * FROM users WHERE user_uid='$uid'";
                      $result = mysqli_query($conn, $sql);
                      $resultCheck = mysqli_num_rows($result);

                      if($resultCheck > 0) {
                         header("Location: ../signup.php?signup=usertaken");
                         exit();
                      } else {
                         // Supply a random generated token for email activation

                      	$token = 'qqewreqreqwsdfdfdafcbvcQERFGHFGHGFHRETERTDF!@#$%^^()';
                      	$token = str_shuffle($token);
                      	$token = substr($token, 0, 10);

                      	//Hashing the password
                      	$hashedPwd = password_hash($password, PASSWORD_DEFAULT);
                      	//Insert the user into the database
                      	$sql ="INSERT INTO users (user_first, user_last, user_email, user_uid, user_password, user_permission, user_access, user_token, user_activate) VALUES ('$first', '$last', '$email', '$uid', '$hashedPwd', '$user_permission', '$user_access', '$token', '$user_activate');";

                      	$name = $first, $last;
                        $to = $email;
                        $header = 'From: pianocourse101@hotmail.com';
        
                        $subject = 'Email activation is required';
                         $message = <<<EMAIL

                        Hello $name, 


                        Thanks for registering with PianoCourse101!
                        Please activate your account below before you  
                        can login. Activate your account by clicking   
                        on the following link below....

                         http://localhost/loginsystem/includes/activate.php?email=$to&activatetoken=$token

EMAIL;
       
        
       

                        mail($to, $subject, $message, $header);



                      	mysqli_query($conn, $sql);

                       // insert into permissions table

                      	





                      	header("Location: ../signup.php?signup=success");
                      	exit();
                        }
                 }
   	   	  }
   	   }
   	 } 
}

Should I do something like this

$user_permission = 1;
$user_access = mysqli_real_escape_string($conn, $user_access = 1);
$user_activate = mysqli_real_escape_string($conn, $user_activate = 0);

2

What’s the second assignment for? Makes no sense. What you should do is escape each and every parameter that you use in your SQL statement, just like Prepared Statements do - just have a research for SQL Injection, that explains everything.

3

Why??

Forget mysqli_real_escape_string(), use prepared statements and be done!

1 Like
4

I am just curious about how mysqli_real_escape_string works but I understand that prepared statements are preferred…

5

The escape string functions are a remnant from before mysqli_ - improved.

They were important because without escaping something like this (non-inclusive) example

<script>all_your_cookie_are_mine()</script>

the string might be seen as a tag and the script run.

With escaping the output would be the literal characters - left angle, s, c, r, i, p, t, right angle - and the script would not run.

In many ways mysqli_ was a middle-ground that allowed both the old ways and the improved ways. Although it could be a good idea to learn about the old ways it seems foolish to write new code using them.

2 Likes
6

I don’t think that’s the case. People who tend to do this usually don’t care about security. mysqli_real_escape_string was used to escape user input however, this was the past. But you want to use modern technology because technology evolves so rapidly. If you stick to using mysqli_real_escape_string or $mysqli->real_escape_string, then you are leaving yourself the possibility of vulnerabilities. Sooner or later, mysqli_real_escape_string and $mysqli->real_escape_string will be removed completely from the code base. Prepared statements will most likely stay until there is a new way of handling user inputs in a safe way.

Also, the advantages of using prepared statements outweigh the advantages of the real_escape_string functions. For one, you don’t have to wrap your variables in 50 lines of mysqli_real_escape_string functions. This makes it easier for yourself to work with because you don’t have to manually escape them anymore. You can simply use prepared statements and it’ll escape it for you. It takes probably 4 - 5 lines of prepared statements when it takes 8 - 12 lines of mysqli_real_escape_string to all your user input. It also depends on how much user input you are expecting as well. If you are taking in 1 user input, then fine. But if you are taking in 12 user inputs, that’s going to be 12 lines of manual escaping which wastes time and money if you are working for a client.

Another advantage of using prepared statements over the real_escape_string functions is that you aren’t literally putting the user input right into the query. So if you forget to escape the user inputs with real_escape_string functions, then you are screwed. But using prepared statements, you don’t forget this because you are escaping the input already in the query by using the placeholders.

Prepared statements aren’t hard to learn, just that people are either lazy or don’t care about security. The manual has examples on how to use prepared statements. They are really easy to understand. I’m not going to lie to you because I was once in your shoes. Before understanding PHP, I was a nooby like you. It took me years to understand just a few basic functions. Until I started accepting the real reality, that’s when I started to understand it more.

1 Like
7

I confess that when I was much the newbie my code was not the best. I would not classify my former self as being either lazy or uncaring about security. But most definitely largely ignorant and I didn’t know how much I didn’t know. I wrote all kinds of messy spaghetti code as my understanding grew in hopes of improving security (datatype casting, regex, escaping, character replacing, etc. etc.), only to discover that my attempts were less than adequate and would prove futile in stopping something I had not foreseen.

This is not to say that custom validation and custom sanitation code has no place. Only that using prepared statements, the PHP filters, PHP password hashing, can do a much better job at anything I wrote myself and save me from needing to do a considerable amount of work.

2 Likes
8

Thanks for the information guys! I have followed the mmtuts on prepared statements but just like to practice mysqli_real_escape_string at the moment for my own personal learning… Cheers!

9

I still ask why? It is obsolete, you are wasting your time learning things from the past which will no longer be used (by anyone who knows what they are doing) in the present or the future.

The project you have set yourself is very ambitious for a beginner, you have a huge amount to learn. I don’t see the point in wasting your time learning obsolete functions and out-dated security methods that you should never be using.
Your time would be much better spent learning current best practice, things that you will actually use in a project.

In all honesty, I think your first step should be to learn HTML and become competent in that before even considering learning the more advanced topic of PHP.
Take it one small step at a time and avoid legacy code at all cost.

2 Likes
10

Sorry, but no. What you’re describing there is output escaping, and that isn’t what mysql_real_escape_string did at all. What is did was prevent SQL injection, which is something completely different.

To get from <script> to &lt;script&gt; you’d need html_entities, which is still true today, also with prepared statements.

FIEO: Filter Input, Escape Output
Always

2 Likes
11

I thought I might be able to sneak that one past by using “non-inclusive” but now that I’ve been caught I must agree that example is indeed about entering special characters as entities and not escaping and explain a bit more.

A proper example would be like if someone entered “Conan O’Brien” and because of the quote mark only “Conan O” was entered. (not as dramatic an example as the spectre of cookie theft but more correct).

For some reason, I didn’t think to use the classic Bobby Tables example, which is a dramatic example.

https://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables

1 Like
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.