Click here to Skip to main content
15,910,603 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
           SqlConnection con = new SqlConnection("Data Source=DESKTOP-J7D5POF;Initial Catalog=ilswork;Persist Security Info=True;User ID=*****;Password=**********;Connect Timeout=60");
           con.Open();
           string query = "UPDATE tabl set username='" + txtusername.Text + "',status='" + comboBox1.Text + "',Time='" + txttime.Text + "',clock='" + txtclock.Text + "',type='" + textBox2.Text + "' where CIVILIDD='" + txtCIVILIDD.Text + "'";
           SqlDataAdapter sda = new SqlDataAdapter(query, con);
           sda.SelectCommand.ExecuteNonQuery();


           con.Close();
           mycon.Close();
MessageBox.Show("record updated successfully");


What I have tried:

i want to learn the safety or correct way
Posted
Updated 8-Jan-19 9:11am
v2

Not like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?


Quote:
what is the parameterized queries should i do to my code


Here's a sample:
C#
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("UPDATE myTable SET myColumn1=@C1, myColumn2=@C2 WHERE Id=@ID", con))
        {
        com.Parameters.AddWithValue("@ID", id);
        com.Parameters.AddWithValue("@C1", myValueForColumn1);
        com.Parameters.AddWithValue("@C2", myValueForColumn2);
        com.ExecuteNonQuery();
        }
    }
 
Share this answer
 
v2
Comments
el_tot93 8-Jan-19 12:04pm    
what is the parameterized queries should i do to my code
OriginalGriff 8-Jan-19 12:26pm    
Answer updated.
el_tot93 8-Jan-19 12:29pm    
what is the strConnect
OriginalGriff 8-Jan-19 12:40pm    
How many guesses do you want? :laugh:
Think about the name, and you might be able to work it out...
el_tot93 8-Jan-19 12:04pm    
or how it will me like can you let me see you can do it
To add a few points to previous answers
- Use using blocks for all disposable objects
- Keep the connection open as short time as possible
- Use try..catch blocks for errors
- Investigate return valus

And of course everything that has already been pointed out.

If interested, have a look at Properly executing database operations[^]
 
Share this answer
 
C#
string query = "UPDATE tabl set username='" + txtusername.Text + "',status='" + comboBox1.Text + "',Time='" + txttime.Text + "',clock='" + txtclock.Text + "',type='" + textBox2.Text + "' where CIVILIDD='" + txtCIVILIDD.Text + "'";

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
 
Share this answer
 
Here are the basics of what this looks like with parameters
C#
SqlConnection con = new SqlConnection( /*your connection string*/ );
			
string query = "UPDATE tabl SET username=@username, status=@status, Time=@Time, clock=@clock, type=@type WHERE CIVILIDD=@CIVILIDD";

SqlCommand cmd = new SqlCommand(query, con);

cmd.Parameters.AddWithValue("@username", txtusername.Text);
// continue with the rest of parameters

con.Open();
cmd.ExecuteNonQuery();
con.Close();


1- Obviously you will need to fill in the rest of the parameters.
2- The AddWithValue method will set up the SQL values (nvarchar, int) based on the values types being applied in (eg txtUsername.text)
3- You didn't need a data-adapter, just an Sql Command
4- I would recommend utilizing a using block to encapsulate the SqlConnection object. This will make clean-up easier.
 
Share this answer
 
Comments
el_tot93 8-Jan-19 12:40pm    
you recommend utilizing a using block to encapsulate the SqlConnection object how is that
el_tot93 8-Jan-19 13:27pm    
Error converting data type nvarchar to bigint.
MadMyche 8-Jan-19 15:13pm    
AddWithValue() adds the items in with an SQL type based on the type you put in; if an item coming in from a text box needs to be an Sql BigInt then you will need to convert it prior to or when adding { eg (Int64)textbox.text }
Wendelius 8-Jan-19 14:24pm    
Extremely valid points.
el_tot93 8-Jan-19 14:43pm    
it is not valid i gave me that error

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900