Blog Archives

Is your brain faster than your PC?

Computer vs the brain

I wrote a post quite some time ago illustrating methods that humans can use to solve multiplication faster than a PC.

The methods are very simple and involve practice over a weekly (or monthly) period of time, with time varying depending on the last time you had any math courses.

If you have practiced these methods, you are witness of the proof of human innovation firsthand, yet technically speaking, your PC computes the results much faster, and most benchmarked speed differences are mostly relative to the speed that the computation is entered into the PC.

As the numbers get larger, your ability to pinpoint an exact value becomes increasingly difficult, similar to a PC. However, I have yet to see a PC compare to the estimating abilities of humans involving complex computations.

Enrico Fermi is a case in point. A physicist and mathematician, Fermi made and proposed solutions involving estimates that even today’s PCs would have trouble deliberating. One such example is the Fermi paradox; a mathematical counter-analysis made by Fermi in response to the Drake Equation – an estimate involving extraterrestrial life in our galaxy.

The drake equation is both mathematical and relatively computable (by machines with degrees of accuracy), as you can clearly see if you take a look at it.

There is a catch. Ultimately the results of the equation are of such a large scale, that the mathematical rules of indeterminate form apply, and while the computer is chugging along at finding an accurate result, you can come to the same approximate answer by simply saying the result is either “very big in the billions” or “0”.

While computers may be very good at getting exact results, likely faster than you can, a more interesting and probably also more applicable question is: can you computer determine the best degree of estimation?

The next time someone asks you who can compute faster – the brain or the PC (or Mac), you can safely quote me in response with something such as:

The PC is faster at many math problems, but when it comes to a fermi problem, the PC slows to a grinding halt.

After all, computers can currently only solve what we have designed them to. How could they possibly compute estimations at the same level as our brain if we do not understand the limitations of our own design?

References
Mentally Calculate… (ronniediaz), https://ronniediaz.com/2012/01/19/mentally-calculate-percentages-of-large-numbers-quickly-without-using-a-calculator/
Enrico Fermi (wiki), http://en.wikipedia.org/wiki/Enrico_Fermi
Fermi Problem (wiki), <a href="http://en.wikipedia.org/wiki/Fermi_problem
Drake Equation (wiki), http://en.wikipedia.org/wiki/Drake_equation
Indeterminate Form (wiki), http://en.wikipedia.org/wiki/Indeterminate_form
Computers vs. Humans (blog image), http://rksharma.wordpress.com/

How to Create secure relational databases in SQL

The first step to a secure DB, is a well structured DB and the application layers that access it.

Take the following example of a table containing factories and the robots they produce.

 FACTORYNAME ROBOTNAME FACTORY1 Ron FACTORY1 John FACTORY2 Bob FACOTRY2 Bill

This serves a good purpose by itself, and allows for easy manipulation and viewing of the stored data, but this table could very quickly grow out of hand. What if you needed to store the parts that are required to make each robot? Or if some factories share the types of robots that they can create?

First, lets separate the data into two separate tables of factories and robots:

“SP_GET_FACTORIES”

 FACTORYID FACTORYNAME 1 FACTORY1 2 FACTORY2

“SP_GET_ROBOTS”

 ROBOTID ROBOTNAME 1 Ron 2 John 3 Bob 4 Bill

In order to group these two together. we now require a mapping table.
“FactoryRobotMapping”

 FACTORYID ROBOTID 1 1 1 2 2 3 2 4

To see this data in a single set, like your original table, you would run a select statement with a join. Some programmers put these statements with joins directly in their code as plain text while others keep them in the database.

Relatively newer technologies, such as Microsoft Linq or Ruby have their own syntax for performing these joins and queries which is ultimately translated to plain sql behind the scenes.

Unfortunately, this often leads to a common misconception that because you are using Linq, Ruby or another data access layer that your work ends there.

Consider the following scenario:
App A->retrieves a join of factories and robots
App B->also retrieves the same join

To make this simpler, you create a new project (this would be your data access layer) and add a new method called “GetRobotsInFactories” which uses Linq, plain sql, ROR or some other technology to run the select with the join.

So now you have:
App A->DAL->GetRobotsInFactories
App B->DAL->GetRobotsInFactories

For future changes, simply modify GetRobotsInFactories, upload the DLL and that’s it! Or is it?

What if a new business change now requires you to only get robots in factories after specific manufacture dates?

You could just add an optional parameter to GetRobotsInFactories, but now you have to code in conditional logic to your function checking if the parameter is null, and forming a different query based on this. If there are 10 more parameters like this, your function could now very quickly become much more troublesome to support, especially when certain combinations of parameters may be required.

This creates a weaker structure, and therefore opens the door to security vulnerabilities. The approach on separating the DAL into a new project and wrapping everything in here is great, but you must be very careful on how your DAL accesses the DB.

What’s the alternative?

Views are joins

For starters, creating views in the database for your joins will save you from having to constantly type out select statements with joins and improve your productivity in database debugging greatly.

An example of such join for our above is:

“VW_JOIN_FACTORIES_ROBOTS”

```select * from factories inner join robots on
factories.factoryid = robots.robotid
```

This now also allows you to perform specific select statements against the view, which will look very similar to your first table you created before segregating the data.

Cool, now what?

You might consider plugging in a simple “select * from VW_JOIN_FACTORIES_ROBOTS” into that GetRobotsInFactories function, but not done yet.

Stored Procedures offer security

Many developers understand the minimal amount of SQL required to power their app, or have heard of or created and modified views as well as stored procedures, but often are not aware of the security benefits.

Stored procedures encapsulate the data you need to retrieve. This allows you to control not only the tables being modified, but the way they are modified, dependencies, constraints, parameters and even database permissions on who can execute or modify which stored procedures and ultimately obfuscate your underlying data from would be attackers or meddlers.

Consider the following stored procedure:

“SP_GET_ROBOTS_IN_FACTORIES”

```USE [RobotCo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIED ON
GO
CREATE PROCEDURE [dbo].[RobotCo] (@MfgDate datetime)
AS
BEGIN
SET NOCOUNT ON;
select * from VW_JOIN_FACTORIES_ROBOTS where datetime between @MdfDate and getdate()
END
```

You can now change your “GetRobotsInFactories” function to call “SP_GET_ROBOTS_IN_FACTORIES” and simply pass in date as a parameter to the SP. Future changes will only require you to add parameters to the SP and then simply update the DAL/dbml or definition if you’re using linq/ror/etc.

Locking it down

All structures are already in place! You’re next steps are to make sure the applications have their own SQL user accounts with only access to execute the specified stored procedures.

Without direct access to the view or the tables, exploitation of the app will prevent attackers from retrieving or manipulating the data directly.

Hopefully this offered some insight on database level security, as well as understanding some of the benefits of view and SP utilization for better overall software architecture.

References
Microsoft Linq, http://msdn.microsoft.com/en-us/netframework/aa904594
Ruby on Rails, http://rubyonrails.org/