Securing a database

Deliverance XXV

Hi all,

I am currently doing an advanced database project for college. Security is not a prerequisite but there are marks for including other functions outside of the spec.

I am using parts of this tutorial and I just have a few questions regarding it.

Is using the mysql_real_escape_string function enough to prevent basic sql injections?

[PHP]mysql_real_escape_string($_POST) [/PHP]

Is using the md5 function enough to secure the password?
[PHP]
(md5($_POST)[/PHP]

I have also noticed that they don't close the connection (mysql_close($connectionvariable)) in their tutorial for the log in script. Is this needed to help with the sessions or should it be used?

Thanks for your time - Much appreciated

amen

don't use MD5. Its no longer trusted.

http://en.wikipedia.org/wiki/MD5

Kavrocks

Is using the mysql_real_escape_string function enough to prevent basic sql injections?

Basic SQL injection, yes.

Although most people would have some sort of function to completely sanitize user input.

Is using the md5 function enough to secure the password?

Mostly SHA1 is being used at the moment and a lot of people will add a salt (random string) to the password before encrypting it.

amen

Although we have discussed securing the database in terms of SQL Injection and Password hashing you also need to make sure that the Database is behind a firewall, that the correct ports are open, that Admin account has a hard password, that the relevant users that connect to the database have the correct roles i.e. a user account should only be able to run say inserts but not drop table etc

Deliverance XXV

Thanks guys for the input.

amen wrote: »

Although we have discussed securing the database in terms of SQL Injection and Password hashing you also need to make sure that the Database is behind a firewall, that the correct ports are open, that Admin account has a hard password, that the relevant users that connect to the database have the correct roles i.e. a user account should only be able to run say inserts but not drop table etc

It will only be a project using the local servers on localhost/xampp so it won't be going live. There will only be one account - the admin.

Any recommendations on the salt? Would a general String be enough? If there were more than one user would you need to use different Strings?

[PHP]$salt = "this is an salt";
$password = 'this is an password';
$hash = sha1($salt.$password);
[/PHP]

Any up-to-date and concise links to sanitizing user inputs? Most I've found are just old forum posts squabling over different techniques.

Is not closing the db connection an issue?

ntlbell

Have a look over

http://dev.mysql.com/doc/refman/5.6/en/security.html

Kavrocks

Deliverance XXV wrote: »

Any recommendations on the salt? Would a general String be enough? If there were more than one user would you need to use different Strings?

A random string containing letters, numbers and symbols will be better for the same reason a password containing them will me.

Any up-to-date and concise links to sanitizing user inputs? Most I've found are just old forum posts squabling over different techniques.

CodeIgniter (PHP MVC Framework) has a nice built in function to do this, could be a good place to start but you would have to trawl through the code to find it.

conor.hogan.2

Using prepared/parameterized statements seems to be the general consensus from what I have found searching online so using PDO in PHP for example.

Deliverance XXV

Thanks for all the suggestions folks.

I was just thinking about my own server-side validation that would look for certain keywords and then replace them.
Would this following search/replace code, along with other keywords, be enough to prevent most forms of SQLi? As well as other security measures such as escaping input, of course.

[PHP]<?php

$exampleInput = "Hi, this is an example input from a HTML form. Drop table! Delete table.";

if (( stristr ( $exampleInput, "DELETE" ) ) || ( stristr ( $exampleInput, "DROP" ) ))

$string = str_ireplace( "DELETE", "DEL_ETE", $exampleInput );
$string2 = str_ireplace( "DROP", "DR_OP", $string );

echo $string2;

?>[/PHP]

The output becomes:
Hi, this is an example input from a HTML form. DR_OP table! DEL_ETE table.

Also, in theory, if I was to create a mysql user that only has SELECT and INSERT priveleges then no form of SQLi could allow malicious code to drop/delete tables?

Thanks again

Deliverance XXV

I should also add that the project I am doing it for has a backend function that would allow me to edit any inputs before they become approved but after they hit the database.

So, I could edit any accidental/innocent uses of targetted keywords back to the original context whilst deleting any malicious entries.

fergalr

Deliverance XXV wrote: »

Thanks for all the suggestions folks.

I was just thinking about my own server-side validation that would look for certain keywords and then replace them.
Would this following search/replace code, along with other keywords, be enough to prevent most forms of SQLi? As well as other security measures such as escaping input, of course.

[PHP]<?php

$exampleInput = "Hi, this is an example input from a HTML form. Drop table! Delete table.";

if (( stristr ( $exampleInput, "DELETE" ) ) || ( stristr ( $exampleInput, "DROP" ) ))

$string = str_ireplace( "DELETE", "DEL_ETE", $exampleInput );
$string2 = str_ireplace( "DROP", "DR_OP", $string );

echo $string2;

?>[/PHP]

The output becomes:
Hi, this is an example input from a HTML form. DR_OP table! DEL_ETE table.

I'm not familiar with current webby best practice, but that sort of thing is almost always the wrong way to do things.

You want to make it that no matter what comes in, from the untrusted parts of the system, they can't cause you trouble.

So, no building queries with anything that comes in untrusted parts of the system, unless that input is properly escaped by some sort of library, or code, whose job it is to properly escape things. This gatekeeper code will have been made by professionals, who figure out how to properly escape things, or write DB connectors, for a living. (i.e. not web developers, like you).


As a general security principle (which isn't relevant here, because you will go and get a library to escape things / build-the-query for you), you don't want to be comparing input to a list of know BAD_THINGS, and accepting it if it hasn't got one of the BAD_THINGS. You will always forget a BAD_THING.
Instead, you want a small list of GOOD_THINGS, and anything not on the list, is rejected by default.

CuAnnan

For Sanitising SQL in PHP, I tend to use adoDB and prepared Statements.

$stmt = $conn->Prepare('insert into table (col1,col2) values (?,?)');
$conn->Execute($stmt, array($value1, $value2));

This is not only inherently more secure, it runs faster against multiple values, on account of it being a prepared statement.

Deliverance XXV

Thanks for the useful replies guys. This should be very helpful for future users searching for similar material on Boards.

CuAnnan wrote: »

$stmt = $conn->Prepare('insert into table (col1,col2) values (?,?)');
$conn->Execute($stmt, array($value1, $value2));

Thanks So, this combined with escaping fields and the below MSQL user priveleges should in theory result in a fairly secure database, then?

Can anyone confirm that the following would work or would be good practise?

Deliverance XXV wrote: »

Also, in theory, if I was to create a mysql user that only has SELECT and INSERT priveleges then no form of SQLi could allow malicious code to drop/delete tables?

FSL

Try it. Create a table, set up a user with just select privileges and then from that user execute a select statement with an SQLi drop table in it.

stevenmu

It's always good practice for your users to only have the bare minumum permissions required. Keep in mind though that you can't always just apply this at the database level, in many cases you will need to be more granular. In an ecommerce site for e.g. users may need to be able to delete rows from the ShoppingBasket table, but should never be able to delete from the Products table.

You should also be aware that permissions alone are generally not considered to be enough. You should take a layered approach, even though you might be denying users the permission to delete, you should also prevent them entering delete statements.

ressem

Are there any circumstances where you need to use escape string or addslashes together with prepared statements?

I was under the impression that the prepared statements alone would suffice, with some checking in code to ensure that the size of the parameters are limited to a sensible size.
Escape string in some cases have been evaded using some messing around with unusual character sets.


For some sensitive tables you might want to use stored procedures rather than prepared statements.

Stored procedures are less compatible across different databases. They have an advantage that a low privilege user can call a procedure.
Then the database can run the procedure internally as a higher privilege user.

For example, to check that a username + password hash matches the stored values and return a true / false value without the user needing read permission on the table.

Depending on the complexity of your program, stored procedures might be cumbersome to define for any more of the essentials of your database needs or we mightn't have the SQL skills to efficiently do all the things we can do with prepared statements + php. Debugging them tends to require commercial addons like Toad (free for mysql) or DBForge.
They can enhance security if used correctly though some devs really hate putting complex routines into databases as it makes for non-standard ORM code which can't be automatically generated.

(Not a DBA by the way)

CuAnnan

ressem wrote: »

Are there any circumstances where you need to use escape string or addslashes together with prepared statements?

I don't believe so. In fact, I believe there is not. But I am open to correction on that.

ressem wrote: »

I was under the impression that the prepared statements alone would suffice, with some checking in code to ensure that the size of the parameters are limited to a sensible size.

Well, if you're using something like ado, it's going to throw an exception at you if you try to bind an incorrect number of variables to it.

ressem wrote: »

For some sensitive tables you might want to use stored procedures rather than prepared statements.

I hate sto procs. They blur data storage and computer programming and very tightly couple your data storage with your model.

amen

I hate sto procs. They blur data storage and computer programming and very tightly couple your data storage with your model.

and what are you using in-line sql which is dotted around the code and is tightly coupled to your data model because if it wasn't how are your saving the data.

What exactly is wrong with SPs? A nice optimised pre-compiled procedure that provides specific and defined functionality on accessing the data that can be easily and quickly modified and and have specified read/update/insert etc permissions applied.

CuAnnan

amen wrote: »

and what are you using in-line sql which is dotted around the code

I never have my SQL statements "dotted around the code". They are all in the data access package, inside classes relevant to their model.

amen wrote: »

What exactly is wrong with SPs?

The next guy who comes along to change/fix your code has to figure them out.
They blur the line between application and data storage.

amen wrote: »

A nice optimised pre-compiled procedure that provides specific and defined functionality on accessing the data that can be easily and quickly modified and and have specified read/update/insert etc permissions applied.

See, if I rephrase your words to take out the adjectives or replace them with equally true but negative ones, your point doesn't actually stand.

For example, if I remove all of your opinion from that post and replace it with my opinion, we get:
"An ugly as hell procedure that could have been a prepared statement provides confusion and blurs functionality that can be a nightmare to change and and have specified read/update/insert etc permissions applied but could have been a prepared statement."
What we're talking about here is preference. Stating your preference as an objective truth is not actually doing anything to help the topic.

There are pros to StoProcs, I will admit (efficiency).
But they are, in my opinion, outweighed by the cons (coupling).

amen

We shall have to agree to disagree!

CuAnnan

amen wrote: »

We shall have to agree to disagree!

Unless you're saying that it's not a matter of preference and that StoProcs are inherently superior to PreparedStatements, then I think we're actually agreeing rather than agreeing to differ.

ressem

CuAnnan wrote: »

Unless you're saying that it's not a matter of preference and that StoProcs are inherently superior to PreparedStatements, then I think we're actually agreeing rather than agreeing to differ.

No, I'll agree with your con list.
But sometimes implementing a solution with prepared statements alone would require a whole new proxy tier in front of the database which might not be permissible by the spec.


Mysql has authentication based on username + password + host / ip and a few authentication modules like auth_socket.

So in a case where you can't have the business logic layer / DAL on a separate machine + apache user to the presentation layer, a compromised presentation layer could emulate a lower layer to read tables that are secret. The only defense is the obscurity of the mysql user+password.

Stored procedures can reduce this threat by blocking the machine from anything other than execute SP.

I.e the savings from avoiding SPs turns into writing fallible mysql proxy rewrite code to block "select * from billing_details".

CuAnnan

ressem wrote: »

So in a case where you can't have the business logic layer / DAL on a separate machine + apache user to the presentation layer, a compromised presentation layer could emulate a lower layer to read tables that are secret. The only defense is the obscurity of the mysql user+password.

Yeah, but there are other RDBMS for that.

Draupnir

CuAnnan wrote: »

Unless you're saying that it's not a matter of preference and that StoProcs are inherently superior to PreparedStatements, then I think we're actually agreeing rather than agreeing to differ.

So a deployment of your site is required to fix bugs in database code? That kind of release process has no place in enterprise.

CuAnnan

Draupnir wrote: »

So a deployment of your site is required to fix bugs in database code?

Possibly, as would a StoProc approach.
Because, you know, if the format of the data returned by the sto proc changes, so too does the code that deals with the returned data.

Draupnir wrote: »

That kind of release process has no place in enterprise.

[Citation required]

Draupnir

CuAnnan wrote: »

Possibly, as would a StoProc approach.
Because, you know, if the format of the data returned by the sto proc changes, so too does the code that deals with the returned data.


[Citation required]

But if the format of the data doesn't change, just the underlying logic for returning the data, then a Stored procedure change wouldn't require a full deployment of the application.

I'm not advocating a blanket move of all business logic to database programming, but I absolutely advocate never having SQL in application code.

I appreciate that in some systems it is difficult to avoid, i.e. working with database that don't support stored procs, but I would look at alternative methods in those situations that provide decoupling of database code from the application.

CuAnnan

So, your "this is always true" has just become a "this is sometimes true, and when it's not you're going to have to do what I was arguing should not be ever seen in enterprise situations despite it being a published standard".
Excuse me while I reducto ad absurdum, but I'm getting rather frustrated at people espousing their personal preferences as objective truth, with no regard for the claims of the other side.

At no point did I say that using PrepExes were better than using StoProcs. I stated why I prefer them.

You raised a claim that the Data Access Model "has no place in enterprise".

When pressed for citation for that, rather extraordinary, claim you did not provide it.
So I am going to explicitly ask now why, if it has no place in enterprise, has Java provided a paradigm specifically as part of J2EE for just this model?

You raised a claim that the Data Access Model requires a site deploy, rather than the deployment of between one and three files. When it was pointed out to you that StoProc changes can cause precisely the same problem, your response is "only sometimes"?

Draupnir

Stall the digger there for a second, I think we are talking at cross purposes. SQL code in application code has no place in enterprise, i.e. hardcode SQL in strings that are then executed.

Actual framework paradigms and mechanisms are a different thing altogether. LINQ for example is a very interesting technology in that area, which I am growing more fond of on a daily basis.

Draupnir

CuAnnan wrote: »

You raised a claim that the Data Access Model requires a site deploy, rather than the deployment of between one and three files. When it was pointed out to you that StoProc changes can cause precisely the same problem, your response is "only sometimes"?

I didn't claim that, my point is that if you've got SQL code dotted around your solution then you require full site deploys to address issues.

I am proponent of a multi tiered architecture, if you are building a proper DAL that is deployable separately to the entire site or application then that's absolutely correct.

So again, I suspect we are talking at cross purposes.

CuAnnan

Draupnir wrote: »

I didn't claim that, my point is that if you've got SQL code dotted around your solution then you require full site deploys to address issues.

I addressed the fact that my SQL code is not dotted around the place before you posted.
So either you're not reading my posts but deciding to argue with me or you're arguing with me out of context using terrible arguments.
Either way, I'm not inclined to read your posts anymore until you do me the courtesy of reading mine.