-1

I am trying to check if an Id number exist in MySQL DB with stored procedure. This is my procedure:

CREATE DEFINER=`mm`@`%` PROCEDURE `tts`(
IN `Id` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT * FROM login WHERE Id = Id;
END

This is my code for callin procedure:

private void btnEx_Click(object sender, EventArgs e)
{
    try {
        conn = new MySqlConnection();
        conn.ConnectionString = cs;
        MySqlParameter pm = new MySqlParameter("Id", MySqlDbType.Int32);
        pm.Value = Int32.Parse(tbId.Text);
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "tts";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(pm);
        conn.Open();
        if (cmd.ExecuteNonQuery() == 1) {
            MessageBox.Show("OK!");
        } else {
            MessageBox.Show("Failed!");
        }
    }
    catch (MySql.Data.MySqlClient.MySqlException ex) {
        MessageBox.Show(ex.Message);
    }
}

When the button is pressed i get "Failed!". Where I am wrong?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
M.Mihaylov
  • 43
  • 1
  • 8
  • What is `cmd.ExecuteNonQuery()` returning? – mjwills Nov 11 '19 at 11:40
  • @mjwills It returns 0 – M.Mihaylov Nov 11 '19 at 11:45
  • When you execute the stored procedure in MySQL it returns a record, right? – Albert Nov 11 '19 at 12:15
  • @Albert Yes, it returns 1 row – M.Mihaylov Nov 11 '19 at 12:22
  • I think you might want to look at this answer: https://stackoverflow.com/questions/1933855/how-can-i-retrieve-a-table-from-stored-procedure-to-a-datatable Because you are using a stored procedure that returns a table (not update, delete or insert), which makes the ExecuteNonQuery always return -1. – Albert Nov 11 '19 at 12:33
  • i would drop that stored procedure, this stored procedure is *"overkill"* for what you are trying to do here and not justifiable to use here in my opinion – Raymond Nijland Nov 11 '19 at 12:34
  • Even if it _is_ an overkill, stored procedures are not to blame here. :) – Eric Wu Nov 11 '19 at 12:38
  • .. also be aware that using MySQL directly in C# requires you to totally open up the MySQL on the internet.. Also you are exposing your MySQL username and password aswell in the application you ideally should be using a gateway like serversocket-clientsocket implementation or a webservice (REST, SOAP, XML-RPC, GraphQL) to connect to the database so the MySQL username and password is on the server only.. – Raymond Nijland Nov 11 '19 at 12:38
  • 2
    'SELECT * FROM login WHERE Id = Id;' I cannot tell which iid s the parameter and which is the column - mysql treats them both as the column. You should rename the parameter. – P.Salmon Nov 11 '19 at 12:43
  • 1
    @RaymondNijland, I fully agree with your statement about MySQL credentials being exposed in the client application, and about how the infrastructure should be (e.g. WebServices). Also, we _are_ moving offtopic :) – Eric Wu Nov 11 '19 at 12:44
  • to add to @P.Salmon comment `'SELECT * FROM login WHERE login.Id = Id;'` should also work fine in some MySQL versions.. – Raymond Nijland Nov 11 '19 at 12:48
  • *"and about how the infrastructure should be (e.g. WebServices)"* you mean security wise ? @EricWu well because i most off the time use a Chromium-based control in mine C# apps anyway to get rich and easy GUI with html5 , css and javascript .. I think i would try to implement [w3c web auth standard](https://www.w3.org/TR/webauthn/) which can auth on based on usb hardware tokens or windows hello assuming those browser controls already have support for it (not jet tested) .. Also explained here https://webauthn.guide/ – Raymond Nijland Nov 11 '19 at 12:55
  • @RaymondNijland WebServices were just an example of how to implement a better infrastructure (yes, security-wise). Will be sure to check out the W3C rules on these auth modes. Thanks :) – Eric Wu Nov 11 '19 at 12:59
  • but be sure to still to use `https` otherwise as that web auth is still unsecure for the *"man (or woman for that matter we all mostly likely saw the movie the matrix :-)) in the middle attacks"* without using `https` @EricWu – Raymond Nijland Nov 11 '19 at 13:04
  • 1
    Problem is solved using MySqlDataReader insted of ExecuteNonQuery() – M.Mihaylov Nov 11 '19 at 17:29

1 Answers1

0

The issue here is that your procedure returns a DataTable. Executing NonQuery means that the connector will attempt to find a scalar value (int, bool), from which DataTable can't be converted to.

Change your procedure to return a column (such as the Id itself):

SELECT Id FROM login WHERE Id = Id

Then, change your code to check if the Id received is DBNull.Value, which will indicate if the value has been received.

if (cmd.ExecuteNonQuery() != DBNull.Value)
{
    MessageBox.Show("OK!");
}
else
{
    MessageBox.Show("Failed!");
}
Eric Wu
  • 908
  • 12
  • 35
  • Yes, using MySqlDataReader works fine, btw changing selection only to Id does not work, if I use ExecuteNonQuery always returns me 0. – M.Mihaylov Nov 11 '19 at 17:28