Inserting Data in table that contains foreign key

Question

I'm making an application in c# so I wanted to insert data in a table that contains a foreign key from another table.

Like this suppose you have foreign key (category_id) from categories table in your article table then you can insert data using this way.

INSERT INTO articles( article_name, article_content, category_id, img, url ) 

VALUES( '"+textbox1+"','"+textbox2+"' , ( SELECT category_id FROM categories WHERE categories.category_id = '"+textbox5+"') ,"+textbox3+"' , '"+textbox4+"')

Will this work ?

I think your query is wrong your looing for category_id where category_id = textbox value, I think that needs to be categoryText or something else. Yes it would work but you should not build SQL like that you should use paramaters like this:https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.7.2 — Brad, Jan 17 '19 at 18:42
Mr Zach I'm think it will work but since I'm a beginner so I wanted some advices — Devesh Vagal, Jan 17 '19 at 18:46

score 0 · Answer 1 · answered Jan 17 '19 at 18:50

You would do this.

INSERT INTO articles( article_name, article_content, category_id, img, url ) 
SELECT '"+textbox1+"', '"+textbox2+"', category_id, "+textbox3+"' , '"+textbox4+"'
FROM categories 
WHERE categories.category_id = '"+textbox5+"'

although I am not sure why you would have categories.category_id = '"+textbox5+"' and use categories.category_id in the output of the select. Why not just use the categoryId it seems you already have?

But the main problem I see here is that it seems you are not using parameters. If you are using ado.net (ie. SqlConnection, SqlCommand, etc) you should be passing in parameters and not creating a concatenated string. See How can I add user-supplied input to an SQL statement?

Okay I'll try to implement parameters – Devesh Vagal Jan 17 '19 at 19:23 — Devesh Vagal, Jan 17 '19 at 19:23

Jamshaid K. · Answer 2 · 2019-01-17T19:07:26.510

Never ever Concatenate strings to generate a query tattoo this thing on your body. Here is how your code and Queries should interact with SQL server.

    private int InsertRecord(string articleName, string articleContent, string cId, string img, string url)
    {
        int rA;
        using (var con = new SqlConnection(@"YOUR CONNECTION STRING GOES HERE"))
        {
            using(var cmd = new SqlCommand("INSERT INTO Articles (article_name, article_content, category_id, img, url) values (@aName, @aContent, (SELECT category_id FROM categories WHERE categories.category_id = @cId), @img, @url)", con))
            {
                cmd.Parameters.AddWithValue("@aName", articleName);
                cmd.Parameters.AddWithValue("@aContent", articleContent);
                cmd.Parameters.AddWithValue("@cId", cId);
                cmd.Parameters.AddWithValue("@img", img);
                cmd.Parameters.AddWithValue("@url", url);

                con.Open();

                rA = cmd.ExecuteNonQuery();

                con.Close();
            }
        }

        return rA;
    }

ADO.NET is highly vulnerable to SQL Injections if you go by the string concatenation for your query. Another thing is to use Stored Procedures for sql again that will also based on Parameterizing the SqlCommand.

Jamshaid Kamran I'll try this & get back to you thank you for your help. — Devesh Vagal, Jan 17 '19 at 19:21

Inserting Data in table that contains foreign key

2 Answers2