Correct Way To Put Data Into MYSQL

[Pigmaster]

It a place where there are many different pages on many different sites all giving different points of view as to the correct way to input data to MYSQL. Some swear by using spritf, mysql_real_escape_string,filter_var or even filter_input in different formats.

Scenario:

Data from a form (be it GET or POST), lets say the data from the form is POST $_POST("record") and the data is

Fred's record keeping is great

Now in the page that processes the data is the confusing bit.

Should the correct way using the above data just be.

Validate $POST("record") is set using isset
Preform mysql_real_escape_string on $POST("record")
place in MYSQL using INSERT SQL

or

Validate $POST("record") is set using isset
Preform mysql_real_escape_string on $POST("record")
Sanitize $_POST("record") using filter_var with FILTER_SANITIZE_STRING
place in MYSQL using INSERT SQL

or another way. So what is the correct way to make sure that the apostrophes are OK and no nasty HTML code is dumped.

Thanks for helping this confused Man. I just want to make sure the data is secure from attacks.

Also if the data being submitted as HTML with HTML tags what needs to be changed in the procedure to take care of the HTML.

[Weedpacket]

Have a look at [man]PDO[/man]. Safely escaping strings for embedding in queries is a solved problem already - no need to go to the effort of solving it again.

[halojoy]

There are almost as many answers as there are questions to this 🙂
And there are sure several ways of doing it properly.
Using PDO, as already mentioned by Weedpacket, has become very, very popular.
And PHP [man]PDO[/man] supports most databases.

I found this site with lots of information. I will read a bit myself 🙂
"Filtering Data with PHP" http://www.phpro.org/tutorials/Filtering-Data-with-PHP.html

Contents

1. What is a PHP Filter
2. Getting Started
3. Filtering Variables
4. Validating INTEGERS
5. Validate BOOLEAN
6. Validate FLOAT
7. Validate REGEX
8. Validate a URL
9. Validate an IP Address
10. Validate an Email Address
11. Sanitizing Variables
12. Sanitize a String
13. URL Encode
14. Sanitize Special Chars
15. Filter Unsafe RAW
16. Sanitize an Email Address
17. Sanitize a URL
18. Sanitize an Integer
19. Sanitize a Float
20. Magic Quotes
21. Callback Filter
22. The INPUT Filter
23. Filter an array
24. Filter an array with callback
25. A Real World Example
26. Credits

[Pigmaster]

Weedpacket;10939845 wrote:

Have a look at [man]PDO[/man]. Safely escaping strings for embedding in queries is a solved problem already - no need to go to the effort of solving it again.

Never heard of PDO and if I had I can not remember it.

Having a good read now of the link you provided. It is yet another answer to my question just like halojoy mentions though I must admit reseaching my question PDO never came up until now.

Thanks

[Pigmaster]

Have had a good read and understand it some what, however it is quite complicated to implement right now in the here and now and will be suitable for longer term integration.

So, back to using old fashioned MYSQL, what is the best way to sanitize a variable that has the value of

Fred's record keeping is great

before applying mysql_real_escape_string making sure that any rouge HTML does not get through or SQL injection code etc.

Thanks

[bradgrafelman]

You're really asking two questions in one.

The answer to your first question, to prevent SQL injection/errors, is to use a DBMS-specific sanitizing function (or prepared statements, e.g. with PDO or [man]MySQLi[/man]). If you're using the older [man]mysql[/man] library, then yes, [man]mysql_real_escape_string/man is the function to use.

The answer to your second question is... it's up to you. Even if you say that you don't want a piece of incoming data to contain HTML code, there's still more questions to ask. What do you want to do if it contains data that could be interpreted as HTML? Strip out the possible HTML tags? Change '<' and '>' to HTML-safe entities? There are functions/methods of handling these scenarios and many, many more; you just have to come up with your own policy and then use functions to accomplish it.

[Pigmaster]

bradgrafelman;10939926 wrote:

The answer to your second question is... it's up to you. Even if you say that you don't want a piece of incoming data to contain HTML code, there's still more questions to ask. What do you want to do if it contains data that could be interpreted as HTML? Strip out the possible HTML tags? Change '<' and '>' to HTML-safe entities? There are functions/methods of handling these scenarios and many, many more; you just have to come up with your own policy and then use functions to accomplish it.

I have tried many many things over the last 48hrs on what is a simple procedure, I have tried looking at different situations and different solutions. My only problem seems to be that using the built in php filter_var filtering solutions works except it that it converts apostrophes to

's

and then when it get placed in the database the apostrophe does not really exisit thus when you output the text to be edited the apostrophes appears as

's

in the text.

I have been now tried the option FILTER_FLAG_NO_ENCODE_QUOTES in the filter_var function and this does solve my problems.

So I am going with getting the input, sanitize it with

filter_var($This_Variable, FILTER_SANITIZE_STRING,FILTER_FLAG_NO_ENCODE_QUOTES);

then run mysql_real_escape_string for database inclusion.

The above (it my simple few tests) does strip out all HTML tags etc and leave me hopefully with a workable string to redit and display. Hopefully this is the correct way and also usable way of making sure inputs are suitable for working with and secure.