0

I would like to know which is stronger filter_var or mysqli_real_escape_string.

To be more specific.

Option1:

//Course Title
$ctitle = trim($_POST['courseTitle']);
$clean_ctitle = filter_var($ctitle, FILTER_SANITIZE_STRING);

Option2:

//Course Title
$ctitle = trim($_POST['courseTitle']);
$clean_ctitle = mysqli_real_escape_string($con, $ctitle);

I also know about prepared statements but I want to filter the data coming in before hand. If there is an alternative, let me know.

DirtyBit
  • 16,613
  • 4
  • 34
  • 55
code_legend
  • 3,547
  • 15
  • 51
  • 95

2 Answers2

5

They don't achieve the same goal, you will use filter_var($ctitle, FILTER_SANITIZE_STRING) to get rid of unwanted special characters, while mysqli_real_escape_string is a transport encoding to apply needed transformations so that what you will store will be exactly what you provided, avoiding SQL error or injections.

Typically you will first use filter_var() (or any built-in functionality in the framework you use) to enforce the format and allowed characters rules from external input (form data, URL parameters...). Then, when it comes to save the data you validated, you will use mysqli_real_escape_string() to inject the data into your MySQL string.

To summarize,

  • filter_var() is about filtering and validating input data.
  • mysqli_real_escape_string() is about escaping data before using it in a MySQL string.

You're not supposed to choose between them, you're supposed to use both ;-)

TrogloGeek
  • 111
  • 5
2

filter_var and mysqli_real_escape_string are two different things. You haven't really said what you are using the strings for but filter functions are OK, but some of them are more validators than filters.

The FILTER_SANITIZE_STRING filter option removes tags and remove or encode special characters from a string. It's not good sanitation for mysql.

So it depends what you are sanitizing for.

If you are looking to sanitize your strings before using them in MySQL queries, you should indeed use mysqli_real_escape_string. But as you said prepared statements (PDO) is better.

Timmetje
  • 7,641
  • 18
  • 36
  • thank you. I am using the data collected from a contact form to send out an email to us nothing is being stored in the database here. Out of curiosity, why would FILTER_SANITIZE_STRING not be good for mysql sanitation – code_legend Aug 14 '15 at 12:54
  • Because it will corrupt the data. You sanitize your data based on the target. So you don't sanitize HTML for your mysql goal. You simply save the HTML. And if necessary escape it when needed. – Timmetje Jun 29 '18 at 08:58