#How do I display an error when using sqlight to delete a record from a table with an invalid ID?

1 messages · Page 1 of 1 (latest)

ionic fossil
#

I have the following code for deleting a todo from my sqlite table:

pub fn delete_todo(id: String) -> Result(Int, sqlight.Error) {
  let assert Ok(conn) = open_db_conn()
  let sql = "DELETE FROM todos WHERE id = " <> id
  wisp.log_info("Attempting to delete todo with ID: " <> id)

  // use item <- result.try(sqlight.exec(sql, conn))
  let item = sqlight.exec(sql, conn)
  case item {
    Ok(_item) -> {
      wisp.log_info("Todo with ID " <> id <> " deleted successfully.")
      Ok(1)
      // Return 1 to indicate one row affected
    }
    Error(error) -> {
      wisp.log_error(
        "Error deleting todo with ID " <> id <> ": " <> error.message,
      )
      Error(error)
    }
  }
}

But I've noticed that if I pass an ID that doesn't exist it still prints out the log message Todo with ID 10000 deleted successfully. I know that sqlight.exec function returns a Nil or sqlight.Error just wondering why the error is not being called

covert scarab
#

It is not an error to delete something that does not exists in SQL.

covert scarab
#

and you should use a paremeterized query instead of string concatenation, it is just a SQL-injection disaster waiting to happen otherwise.

Use sqlight.query for deleting instead:

pub fn delete_todo(conn, id: String) -> Result(Int, sqlight.Error) {
  let sql = "DELETE FROM todos WHERE id = ? RETURNING id"
  echo "Attempting to delete todo with ID: " <> id

  let item =
    sqlight.query(sql, conn, [sqlight.text(id)], decode.list(decode.string))
    |> echo
  case item {
    Ok([]) -> {
      echo "Todo with ID " <> id <> " not found"
      Ok(0)
    }
    Ok(_) -> {
      echo "Todo with ID " <> id <> " deleted successfully."
      Ok(1)
    }
    Error(error) -> {
      echo "Error deleting todo with ID " <> id <> ": " <> error.message

      Error(error)
    }
  }
}

Note : We are using the RETURNING clause in SQL here to get back a list of IDs which were affected by the query + we are parameterizing the query with a ? and sending the todo as a parameter to the sqlight.query function instead.

There is a changes() function in sqlight you could use to get the "number of affected rows by the previous query", but returning the id's is enough in this case.

SQL injection

In computing, SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker). SQL injection must exploit a security vulnerability in an application's software, for example, when user input is...

grim swallow
#

Also, in this case I think it would be better to use a bool/custom type to represent if there was a deletion or not

ionic fossil
covert scarab
#

your code suggested the ID was a string, so that’s why I put the string decoder there. If it is an int then use the int decoder as the error message says.