Click here to Skip to main content
13,596,975 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
private void btn_search_Click(object sender, EventArgs e)
{
conn.Open();

SqlDataAdapter SDA = new SqlDataAdapter("select dbo.Alteracoes.Data, dbo.Funcionarios.numero as no, dbo.Funcionarios.nome, dbo.Alteracoes.Falta as faltas, dbo.Alteracoes.Coluna2 as horasextras from dbo.Alteracoes inner join dbo.Funcionarios on dbo.Alteracoes.IDFuncionario = dbo.Funcionarios.IDFuncionario inner join dbo.Departamentos on dbo.Funcionarios.IDDepartamento = dbo.Departamentos.IDDepartamento WHERE dbo.Alteracoes.Data '" +dateTimePicker1.Value.ToString("dd/MM/yyyy") + "'", conn);

DataSet dt = new DataSet();
SDA.Fill(dt, "dbo.Alteracoes.Data");
dataGridView1.DataSource = dt.Tables["dbo.Alteracoes.Data"];
conn.Close();
}

What I have tried:

private void btn_search_Click(object sender, EventArgs e)
        {
            conn.Open();

            SqlDataAdapter SDA = new SqlDataAdapter("select dbo.Alteracoes.Data, dbo.Funcionarios.numero as no, dbo.Funcionarios.nome, dbo.Alteracoes.Falta as faltas, dbo.Alteracoes.Coluna2 as horasextras from dbo.Alteracoes inner join dbo.Funcionarios on dbo.Alteracoes.IDFuncionario = dbo.Funcionarios.IDFuncionario inner join dbo.Departamentos on dbo.Funcionarios.IDDepartamento = dbo.Departamentos.IDDepartamento WHERE dbo.Alteracoes.Data '" +dateTimePicker1.Value.ToString("dd/MM/yyyy") + "'", conn);

            DataSet dt = new DataSet();
            SDA.Fill(dt, "dbo.Alteracoes.Data");
            dataGridView1.DataSource = dt.Tables["dbo.Alteracoes.Data"];
            conn.Close();
        }
Posted 3-Jan-18 0:12am
Updated 3-Jan-18 1:05am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Simple: don't do it 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. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
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:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
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?

The query you show isn't vulnerable to SQL Injection, but the rest of your code probably is, and the concatenation is not helping the problem here - fix your whole code, add an equals sign to your WHERE clause, and the problem will go away at the same time.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Seems like you have missed the equal(=) in where clause:

Concatenating the sql Query string is vulnerable to SQL Injection[^] attacks
always use Parameterized queries to prevent SQL Injection Attacks in SQL Server[^]


string query = "select dbo.Alteracoes.Data, dbo.Funcionarios.numero as no, dbo.Funcionarios.nome, dbo.Alteracoes.Falta as faltas, dbo.Alteracoes.Coluna2 as horasextras from dbo.Alteracoes inner join dbo.Funcionarios on dbo.Alteracoes.IDFuncionario = dbo.Funcionarios.IDFuncionario inner join dbo.Departamentos on dbo.Funcionarios.IDDepartamento = dbo.Departamentos.IDDepartamento WHERE dbo.Alteracoes.Data = @date";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.CommandType= CommandType.Text;
                cmd.Parameters.Add("@date", dateTimePicker1.Value.ToString("dd/MM/yyyy"));
                SqlDataAdapter SDA = new SqlDataAdapter(query, conn);
  Permalink  
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.180621.3 | Last Updated 3 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100