ExecuteSqlCommand timing out

Question

db.Database.ExecuteSqlCommand("INSERT INTO [dbo].[CalendarEvent] (Length, Name) VALUES ('" + item.Length + "," + item.Name.Replace("'", "''") + "')");

I am attempting to add a line into a database table in C# Entity Framework but the execute statement keeps timing out before running. (Well i believe timing out as it just stops running.

I have tried both inserts seperatly (e.g. Name and length) and both work but when together it fails. I have also tried inserting one and then trying to update that line but fails in the same way.

Before that line of code, put `var bob = "INSERT INTO [dbo].[CalendarEvent] (Length, Name) VALUES ('" + item.Length + "," + item.Name.Replace("'", "''") + "')";` What is the value of `bob`? — mjwills, Apr 02 '18 at 22:31
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Apr 02 '18 at 22:31
I feel you are not using advantage of ORM(Entity Framework) with your approach -sql insert statement. i suggest do some research on Entity Framework. — user9405863, Apr 02 '18 at 22:46
The Value of Bob is "INSERT INTO [dbo].[CalendarEvent] (Length, Name) VALUES ('3,Josh Test')" @mjiwills — Josh Fletcher, Apr 02 '18 at 22:52
@JohsFletcher You have listed two columns (`Length` and `Name`) but only inserted one of them (the comma is **inside** the quotes). Have a read of the link I provided. Or read up on how to insert objects with EF. Something like https://stackoverflow.com/questions/8835434/insert-data-using-entity-framework-model . — mjwills, Apr 02 '18 at 22:56
Possible duplicate of [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement) — 273K, Apr 03 '18 at 10:12

score 3 · Accepted Answer · answered Apr 02 '18 at 23:00

Your call requires extra quote sign. I removed the one for the numeric data type.

db.Database.ExecuteSqlCommand("INSERT INTO [dbo].[CalendarEvent] (Length, Name) VALUES (" + item.Length + ",'" + item.Name.Replace("'", "''") + "')");

That being said, you have a few problems with it that come to mind. SQL Injection is first and foremost.

I recommend you use the entity framework to create a CalendarEvent object, and instead use its properties. Once you have the class created via the EF Designer, it should look like this instead:

CalendarEvent ce = new CalendarEvent();
context.Entry(ce).State = EntityState.Added; 
ce.Length = item.Length; // I'm assuming this is some number data type.
ce.Name = item.Name; // No replace needed
db.SaveChanges();

I also wanted to mention, not sure if you have an auto-generated PK (or one at all). Also think its odd to save the field length. It's easy enough to get that from SQL using the LEN function (https://www.techonthenet.com/sql_server/functions/len.php).

Okay have fixed that and works now. I have also changed to using the EF designer so thats alot. Also, length needed to be set as it is the length of that specific event type. — Josh Fletcher, Apr 02 '18 at 23:09

ExecuteSqlCommand timing out

1 Answers1