C# MYSQL - I can't insert a boolean value

Question

    private void button1_Click(object sender, EventArgs e)
    {
        // Início da Conexão com indicação de qual o servidor, nome de base de dados e utilizar

        /* É aconselhável criar um utilizador com password. Para acrescentar a password é somente
        necessário acrescentar o seguinte código a seguir ao uid=root;password=xxxxx*/

        mConn = new MySqlConnection("Persist Security Info=False; server=localhost;database=FichasReparacao;uid=root");

        // Abre a conexão
        mConn.Open();

        //Query SQL
        MySqlCommand command = new MySqlCommand("INSERT INTO Cliente (nome, email, telefone, blacklist)" +
        "VALUES('" + nome_cli.Text + "','" + email_cli.Text + "','" + telefone_cli.Text + "','" + false + "')", mConn);

        //Executa a Query SQL
        command.ExecuteNonQuery();

        // Fecha a conexão
        mConn.Close();

        //Mensagem de Sucesso
        MessageBox.Show("Gravado com Sucesso!", "Informação", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

Here's the full button code, I'm not receiving any error messages .. I tried using a variable with a false/true value but nothing, I always get the 0 value.

Do you have any error message or exception? Show your full code. Also, be aware of SQL Injection. Always use parameterized queries. — Soner Gönül, May 17 '13 at 14:06
change your Insert query to utilize Parameters instead of setting yourself up for potential SQL Injection for starters.. provide the schema and or the Table Definition if possible to let us know what the actual datatype is for that field — MethodMan, May 17 '13 at 14:06
You have 'false' (quoted). it's a SQL **keyword** then it **doesn't need quotes**. Addendum: moreover follow DJ KRAZE suggestion too, do not build the sql command but use parameters (especially to avoid sql injection). — Adriano Repetti, May 17 '13 at 14:08
I don't have the false quoted. It gives an error when I do it — Van Droste, May 17 '13 at 14:10
Also do not hard code username and password.. store those as values in a .Config file as well and when you get more advanced use encryption, that section can be encrypted in the .config file as well check out MSDN on that..plenty of examples — MethodMan, May 17 '13 at 14:12
I have to learn about SQL Injection, I don't even know what that is as I'm a begginer, but I'll get there, thanks for the advices — Van Droste, May 17 '13 at 14:13
@VanDroste you have: `..."','" + false + "')"` will be converted to `...','False')`. Note the single quotes around `False`. — Adriano Repetti, May 17 '13 at 14:16
Simply look at the link in my answer. It is a funny explanation — Steve, May 17 '13 at 14:17
basically to avoid using any quotes to build a query string which is not necessary and I would totally avoid doing so like I have mentioned earlier, he needs to use Parameters.. plain and simple.. LOL — MethodMan, May 17 '13 at 14:18

score 6 · Accepted Answer · edited May 23 '17 at 11:49

Use a parameterized query

MySqlCommand command = new MySqlCommand("INSERT INTO Cliente " + 
                      "(nome, email, telefone, blacklist)" + 
                      "VALUES(@nome, @email, @tel, @bl)";
command.Parameters.AddWithValue("@nome",nome_cli.Text);
command.Parameters.AddWithValue("@email", email_cli.Text);
command.Parameters.AddWithValue("@tel", telefone_cli.Text);
command.Parameters.AddWithValue("@bl", 0);
command.ExecuteNonQuery();

In this way the net framework and the ADO.NET provider of MySql work to pass your values to the database engine in the correct way. If, for example, one of your input text contains a single quote, your code will fail with a syntax error. And, if you have a malicious user, you risk a Sql Injection

score 3 · Answer 2 · answered May 17 '13 at 14:11

3

MySQL does not store true and false, the datatype behind BOOLEAN is TinyInt(1)

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

I'd recommend passing across 0

answered May 17 '13 at 14:11

Pete O'Connell

143
1
2
8

C# MYSQL - I can't insert a boolean value

2 Answers2

Linked