when and how should i use this functions?

Question

i have a script in php and mysql and i want to secure its inputs against sql enjection.
i know some functions but really don't know where and how exactly should use them.
functions are:

nl2br()
preg_replace()
htmlspecialchars()
mysql_real_escape_string()
htmlentities()

and my inputs are:

username
password
mobile
email
url
description (text area)

anyone can explain for me what should i do?

thanks

possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Shankar Narayana Damodaran, Aug 16 '13 at 07:47

score 2 · Answer 1 · answered Aug 16 '13 at 08:01

2

You have to use a newer version of the mysql driver called mysqli or you can use an alternative like PDO

You can use the database in combination with prepared statements. That is the only way to defend against sql injection. So take your time to chose one of the classes and learn how to use them.

ps,

Still sql injection is not the only thing you have to worry about. Someone can just add some JavaScript in a public form that will hide the page or something like that. You can use this to filter them out preg_replace('/<script\b[^>]*>(.*?)<\/script>/is', "", $var); but still this is only one example there is so much more to learn just take a look at PDO or mysqli and start building.

answered Aug 16 '13 at 08:01

botenvouwer

4,334
9
46
75

thank you. whats the difference bettwin mysql and mysqli functions? i have used mysql for connections and ... I have to change them? – Saeid Aug 16 '13 at 08:08
The mysql functions are old an deprecated. This means that they are to be removed in the future. Because old scrips use them a lot they are still available for compatibility. So its advised to not use them for new scripts. Also mysqli and pdo are classes and you use them a little different than mysql functions. Just search a good tutorial on youtube and learn how to use one of them. believe me it's worth the trouble! – botenvouwer Aug 16 '13 at 08:15

score 1 · Answer 2 · answered Aug 16 '13 at 07:48

1

the only function you need in context of sql-injections is mysql_real_escape_string()

but keep in mind, that you should use mysqli_* or PDO instead of mysql_*

answered Aug 16 '13 at 07:48

donald123

5,638
3
26
23

What's wrong with PDO? – Barmar Aug 16 '13 at 07:49
Nothing .... told mysqli_* or PDO instead of mysql – donald123 Aug 16 '13 at 07:50
Looks like you're saying mysqli instead of PDO. – Barmar Aug 16 '13 at 07:51
wait :D i will edit it – donald123 Aug 16 '13 at 07:51

user2688153 · Answer 3 · 2013-08-16T09:00:04.257

1

Use

htmlspecialchars()

before showing data to the user. It will escape html symbols. This function will prevent XSS attacks.

Use

mysqli_real_escape_string()

before putting data to the database. This will escape sql symbols. This one will prevent SQL injections.

mysql is depricated; use mysqli extension instead(mysqli_real_escape_string() or mysqli::real_escape_string() in OOP style).

edited Aug 16 '13 at 09:00

answered Aug 16 '13 at 07:49

user2688153

47
1
8

You can only use `mysqli_real_escape_string()` if you're using the rest of `mysqli` -- you can't mix and match pieces of each. – Barmar Aug 16 '13 at 07:54

score 1 · Answer 4 · edited Aug 16 '13 at 08:14

nl2br() — Inserts HTML line breaks before all newlines in a string.

 echo nl2br("foo isn't\n bar"); // Outputs: foo isn't<br />bar

preg_replace() - replace the matching string using regular expression.

preg_replace("/[^0-9]+/","",$string); //Outputs: return only number from any string.

htmlspecialchars() -Convert special characters to HTML entities.

echo htmlspecialchars("<a href='test'>Test</a>"); // <a href='test'>Test</a>

mysql_real_escape_string()-Escapes special characters in a string for use in an SQL statement.

htmlentities()-Convert all applicable characters to HTML entities.

 echo htmlentities($str);

$str = "A 'quote' is <b>bold</b>"; // Outputs: A 'quote' is <b>bold</b>

ciruvan · Answer 5 · 2013-08-16T07:56:20.227

0

First rule against SQL injection: Use prepared statements or parameterized queries. Do not pass user input unchecked into any SQL commands. You can achieve this by using mysqli or PDO, but not using the deprecated mysql library, which you should avoid at all cost.

edited Aug 16 '13 at 07:56

answered Aug 16 '13 at 07:50

ciruvan

5,143
1
26
32

Should be prepared statements _and_ parametrized queries -- You can't do parameters without preparing, can you? – Barmar Aug 16 '13 at 07:53
True, but [this](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) reads the same and got 1800+ upvotes... it's meant like "prepared statements, also called.." – ciruvan Aug 16 '13 at 07:55
That answer says "and". :) – Barmar Aug 16 '13 at 07:57
You could then argue that's also wrong. "Use this.. AND this too!" :D Never mind, I see your point and you are right, sir. – ciruvan Aug 16 '13 at 07:59

score 0 · Answer 6 · edited Nov 11 '13 at 10:00

Yours is a good question that more people should ask.

The principles you should be applying to are known in the computer world by the mnemonic FIEO.

Filter Input Escape Output.

Searching the Internet for "PHP FIEO" will help you organise your thoughts, and then put all these security types of functions onto one side or the other of FI or EO.

When you have read up on it, the replies to this question ought to make some more sense and help identify which functions you need for security and which are simply to make text handling a little simpler (i.e. nl2br() etc)

It will initially be a couple of hours well spent and help you sleep at night, well, it did me anyhow. Good luck!

when and how should i use this functions?

6 Answers6