none
Visual Studio 2017 RRS feed

  • Question

  • how to add a string value to a sql server database table using visual studio.

    i want to insert them likewise i insert string variables to the table.

    ("insert into AuditLog values('"+ip+"','"+user+"','"+action+"' , '" + DateTime.Now + "'")

    using SQL injection method.
    Friday, May 24, 2019 1:31 PM

Answers

  • Here is how to do a insert, get new id, no SQL injection. Date field has a default of the current date/time.

    using System;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void insertButton_Click(object sender, EventArgs e)
            {
                var ops = new DataOperations();
                var id = ops.Insert("xx", "Karen", "New");
                MessageBox.Show($"Id is {id}");
            }
        }
    
        public class DataOperations 
        {
            public int Insert(string ip, string user, string action)
            {
                
    
                string insertStatement = "INSERT INTO AuditLog ([Ip],[User],[Actions]) VALUES (@Ip,@User,@Actions);" + 
                                         "SELECT CAST(scope_identity() AS int);";
    
                using (var cn = new SqlConnection() {ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWindAzure3;Integrated Security=True" })
                {
                    using (var cmd = new SqlCommand() {Connection = cn, CommandText = insertStatement})
                    {
                        cmd.Parameters.AddWithValue("@Ip", ip);
                        cmd.Parameters.AddWithValue("@User", user);
                        cmd.Parameters.AddWithValue("@Actions", action);
    
                        cn.Open();
    
                        return (int) cmd.ExecuteScalar();
                    }
                }
            }
        }
    }

    Table definition

    CREATE TABLE [dbo].[AuditLog](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Ip] [nvarchar](max) NULL,
    	[User] [nvarchar](max) NULL,
    	[Actions] [nvarchar](max) NULL,
    	[Created] [datetime2](7) NULL,
     CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[AuditLog] ADD  CONSTRAINT [DF_AuditLog_Created]  DEFAULT (getdate()) FOR [Created]
    GO


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, May 24, 2019 2:29 PM
    Moderator