The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> SQL Injection prevention
max2474
post Feb 11 2012, 09:20 PM
Post #1





Group: Members
Posts: 5
Joined: 10-February 12
Member No.: 16,443



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 smile.gif
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Feb 12 2012, 07:51 AM
Post #2


Jocular coder
********

Group: Members
Posts: 2,256
Joined: 31-August 06
Member No.: 43



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...


--------------------
Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
max2474
post Feb 12 2012, 08:11 PM
Post #3





Group: Members
Posts: 5
Joined: 10-February 12
Member No.: 16,443



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

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Feb 12 2012, 09:39 PM
Post #4


.
********

Group: WDG Moderators
Posts: 6,174
Joined: 10-August 06
Member No.: 7



QUOTE(max2474 @ Feb 13 2012, 02:11 AM) *

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.


--------------------
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Feb 12 2012, 11:44 PM
Post #5


Jocular coder
********

Group: Members
Posts: 2,256
Joined: 31-August 06
Member No.: 43



QUOTE(Christian J @ Feb 13 2012, 11:39 AM) *

QUOTE(max2474 @ Feb 13 2012, 02:11 AM) *

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.


--------------------
Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Feb 13 2012, 11:17 AM
Post #6


.
********

Group: WDG Moderators
Posts: 6,174
Joined: 10-August 06
Member No.: 7



QUOTE(Brian Chandler @ Feb 13 2012, 05:44 AM) *

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.


--------------------
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Feb 13 2012, 11:19 AM
Post #7


.
********

Group: WDG Moderators
Posts: 6,174
Joined: 10-August 06
Member No.: 7



QUOTE(max2474 @ Feb 13 2012, 02:11 AM) *

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... wacko.gif


--------------------
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 1st November 2014 - 03:14 AM