SQL Injection prevention Options

[max2474]

Hi. Have been reading a lot on SQL injection and it seems there are many ways to help prevent it. I am just wondering, would something like

CODE

$email=filter_var(mysql_real_escape_string($_POST[email]), FILTER_SANITIZE_EMAIL);

be enough? My understanding is that this will ensure there is a "@" sign with data either side, as well as removing the usual sql "baddies". If so, is there still a need to do long scripts for further checking?

Btw, is there anything wrong with combining two checks in one? The script is working fine at the mo.

I have many form variables to check, this is just one example.

Many thanks in advance

[Brian Chandler]

No, no "magic dust" is ever "enough". The whole program has to be written in a security conscious way. (It's a bad idea to get a "working script" first, then add security.)

But all you need to do to prevent SQL injection is to use the mysql_real_escape_string() function on any string you insert in an SQL query. A perfectly valid value may include ' (for example) which needs to be escaped.

The "SANITIZE" thing is about checking that an email address is not maliciously invalid, for when the string is included in a call to mail(). (Usually the thing to watch out for is newline characters.)

There's nothing wrong with doing two things at once, but you need to know *exactly* what the $email variable contains: is it an address? or an address escaped for SQL? or what? The way you are doing it suggests that when you print a message "Mail sent to $email" you may get SQL escape sequences, when you really wanted HTML escapes. Better for $email to be exactly the email address...

[max2474]

Thanks very much for your reply. The script is a v early stages yet, am just beginning the sql stage

This question is centered around a sign up / login script.

I have to admit, i have no idea what the difference between an sql escape or html escape may be...however -

IF the database is written with the output from this sequence (when a new member signs up for example) and later compared to his login (also from this sequence), as far as I can see, they will match up.

I guess the only real question is - would this suffice security wise for a member signup/login script?

This post has been edited by max2474: Feb 12 2012, 08:12 PM

[Christian J]

i have no idea what the difference between an sql escape or html escape may be...

The SQL escape prevents users from manipulating the DB. See also http://en.wikipedia.org/wiki/Sql_injection

The HTML escape prevents users from rewriting the HTML of a web page (by escaping things like "<" and ">" characters), which in turn can be exploited in phishing attempts or to post malicious content on the page without the owner's consent. See also http://en.wikipedia.org/wiki/Cross-site_scripting

In other words you need an SQL escape before accepting user input for the DB, and an HTML escape before using content from the DB (or directly from a form submission or querystring) on a web page.

[Brian Chandler]

i have no idea what the difference between an sql escape or html escape may be...

The SQL escape prevents users from manipulating the DB. See also http://en.wikipedia.org/wiki/Sql_injection

I don't think this is the best way to understand it at all.

It's all about "sloppy programming" -- writing programs which "work", most of the time, most of, sort of. I think sloppy progamming is a bad idea, and the reason for the "SQL escape" is to make your program non-sloppy, so it really does what it is supposed to. Of course sloppy programming has always been popular because it takes less effort, and well, mostly works. Then the problem is that sloppy programming can be exploited, but even before it's exploited it is not doing the right thing.

Suppose you have a database of jigsaw puzzles (or books, whatever) and one field is called 'title', and is the title of the book. Sloppy programming results in a system that works until one of the titles is "That's life". Then your program for displaying all entries with a particular title breaks, because it assembles an SQL request that looks like:

SELECT * FROM book WHERE title = 'That's life'

I hope you can see the error. So if a variable $search_title contains the title, you need to use the derived string

mysql_real_escape_string($search_title)

to put in the SQL query.

Unfortunately, the business of web programming has to deal with a number of completely incompatible schemes for escaping strings. The html escape similarly is basically to make the html page display correctly, not necessarily because someone is going to do something malicious.

[Christian J]

Then the problem is that sloppy programming can be exploited, but even before it's exploited it is not doing the right thing.

...

The html escape similarly is basically to make the html page display correctly, not necessarily because someone is going to do something malicious.

True, even a benevolent user (including the programmer himself) can break things unintentionally, so even if say a form is only accessible by a few trusted users it still shouldn't be written in a sloppy way.

[Christian J]

I guess the only real question is - would this suffice security wise for a member signup/login script?

Another thing you should do is encrypt all passwords (using salt). In addition, any "hardwired" passwords (such as the one for the DB itself) should be kept above the web root.

But I'm not the right person to advice on this...