entity framework vs. SP

Hi,

I was just wondering about the performance of entity framework vs, stored procedures. Have you done any testing with this? I'm assuming entity framework will be slower as compared to stored procedures?

Is nopCommerce more vulnerable for SQL injection attacks when running on entity framework instead of stored procedures?

Thanks!

iam not sure if nopCommerce team have tested the application to avoid SQL Injections
Logically using Stored procedures is a better option : much safer and faster

To understand why use stored procedures in any application, please refer this information

The first part of this information talks about how parameterized queries and stored procedures can help prevent sql injections. the second part will critically analyse whether so acclaimed stored procedures do prevent sql injections.Dynamic SQL query i.e. sql strings embedded in the code, which are formed without properly validating the user inputs are almost 100% vulnerable to SQL injection attacks. Examine this code fragment -

string username = Textbox1.Text;

string query = “SELECT [name], [address] FROM USERS  WHERE [username] = ‘” + username + “‘”;

This code is expected to fetch the user details, based on username. It can be a typical code listed on user maintainence screen. Now a malicious user can input in the textbox badguy’;DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘%
This input renders the final SQL statement as follows:

SELECT [name], [address] FROM USERS  WHERE [username] = ‘badguy’;DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘%’

We can see how a simple harmless query can result in big threat to your database. Parameterized stored procedures can go a long way in protecting your database applications from SQL Injection. Given no input validation, the parameterized stored procedure still does not allow you to gain access to the site.
But sometimes badly written stored procedures do not prevent injections. The important thing to do is use parameters with stored procedures. SQL injection is possible if the dynamic SQL inside the stored procedure is not handled properly. Let us see an example.


CREATE PROCEDURE sp_getUser

@username varchar(200) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT [name], [address] ‘ + ‘ FROM [USERS] Where [username] = ”’ + @username  + ””

EXEC (@sql)

In the above case, the variable @username is directly taken from the user input and concatenated with the string i.e. @sql. The EXEC function is being used which takes string as parameter to execute the SQL statements. Making this stored procedure vulnerable to SQL injections even though the user inputs are passed to it as parameters. The user input is enclosed in the single quotes and concatenated to a string to form SQL query. The problem lies here. Instead of the parameter being a search string to the SQL query, the user input has become the part of the query as it is enclosed inside the single quotes. If the user enters the values as badguy’;DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘% then the final SQL query executed at the server will be

SELECT [name], [address] FROM [USERS] Where [username] = ‘badguy’;DROP TABLE USERS;  SELECT * FROM Countries WHERE name LIKE ‘%’

The user gets no benefit of the parameterised sql. The safer way to execute a dynamic sql in the stored procedure is

CREATE PROCEDURE sp_getUser

@username varchar(200) = NULL

AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT [name], [address] ‘ + ‘ FROM [USERS] Where [username] = ‘

SELECT @sql = @sql + ‘ [username] LIKE @username’

EXEC sp_executesql @sql, N‘@username varchar(200)’, @username

Why is this stored procedure different and safer from the previous one?

   1. The user input is not enclosed inside the single quotes. It is rather being passed as parameter to the SQL statement.
   2. The function sp_executesql is being used to execute with the parameter list and the parameterized SQL statements.

Measures to avoid SQL injection

   1. Validate all input coming from the user on the server.
   2. Avoid the use of dynamic SQL queries if there an alternate method is available.
   3. Use parameterized stored procedure with embedded parameters.
   4. Execute stored procedures using a safe interface such as Callable statements in JDBC or CommandObject in ADO.
   5. Use a low privileged account to run the database.
   6. Give proper roles and privileges to the stored procedure being used in the applications.

Check out this post on Stackoverflow for a comparison between EF, L2S and stored procedures with ADO.NET.

The general consensus is that for most CRUD operations the performance is similar and for bulk operations, straight SQL is quicker.

The biggest difference for me is speed of development. The current data access methods employed in nopCommerce take a long time to develop. Using an ORM allows you to spend more time on domain centric development (the stuff we really care about).

I migrated a data access layer for a large application to NHibernate a few months ago. The original data access layer (using provider model and stored procedures) took about a month to write and test. I achieved the same work with NHibernate in a week.

Also it's quite hard to compare stored procs and an ORM like for like. In NHibernate you also get a number of performance features like 1st and 2nd level caching, future queries and batching.

So overall my opinion is that moving an application like nopCommerce to EF is great move.

Absolutely agree with retroviz

I'm totally with you on the speed and ease of development, and I'm glad it doesn't affect performance.

As far as security is concerned you might want to double check with PCI PA-DSS requirements?

https://www.pcisecuritystandards.org/security_standards/pa_dss.shtml

Are there any plans to become PCI validated BTW?

I also agree with retroviz. but there might be ways to solve this issue while maintaining the same code structure.

Why not develop a set of T4 templates that will generate partial classes with basic code to include as needed in the respective projects of the solution. the pattenrs are almost always the same for the Dal, BL and UI.

hallo,
the reason to use EF for more speed in development is accepted. But from 1.7 DAL and business logic will be mixed in one layer. flexibility to have more than one dataprovider (administrated by web.config) get lost. How can i do that?

What i need is more than one dataprovider!

any idea, how?

thanks in advance

No idea ?
Please help.

Thanks

mundl wrote:

OK I'll have a stab but I've not really used EF yet so it may be the blind leading the blind.

My understanding is that EF should actually make it easier to support other database types, provided there is a database connector available which supports EF. There is, AFAIK, one for MySQL for example - http://dev.mysql.com/downloads/connector/net/6.0.html.

All you'd need to do is use the existing model to generate a new database of your preferred architecture, and the app should just work. It doesn't care what underlying technology is being used as a datastore.

One caveat: I've found several tutorials on the web about how to create a data model from an MySQL database e.g. http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials-entity-framework-winform-data-source.html but nothing yet about generating a MySQL database from an existing model. I know you can generate a MS SQL database from an existing model but I haven't tried it yet. Whether or not this is a feature provided by the MySQL .NET connector I couldn't tell you.

Hopefully this lame attempt will spur somebody who REALLY knows the answer to post ;)

kingboyk wrote:

Try this http://dev.mysql.com/doc/refman/5.0/en/connector-net-tutorials-efmodel-ddl.html