Problem in deleting record

zitroware · August 25, 2019, 4:31pm

Hello,

I have a modal that is integrated with a Data Detail component. I am able to open the modal and show the related record when I clicked on the Delete button from the table on the page. The input element is also able to show the ID of the record correctly. The form element is a Server Connect component that is (from what I know) correctly configured (based on several tutorials and videos I have found). The Submit button is on the Modal footer and is the child of the Server Connect form. The input element’s ID is set to the same POST variable (deleteid).

delete_query

But when I click on the “Yes” button, it always returns HTTP status 500. Below is what I get from Google Chrome:

The error is:
“Conversion failed when converting the nvarchar value ‘{{$_POST.deleteid}}’ to data type int.”

The record ID is an integer in my database table.
I have already tried the following:

Set the POST variable to “Number” from “Text”.
Convert the deleteid variable to Number in the query via formatter.
Changed the ID of the input element to something else.
Changed the input type from hidden to text, then to number.
Recreate the form element on the same page inside the modal.
Created a new test page and created a new table. I have used a delete button directly without using a Modal and without the Data Detail component.
Created a Condition in Server Actions.

server_actions

The record ID I tried to delete is 857, so it will pass the condition and to make sure it is a number. If tried to delete record ID 854, I am able to get the Error 500 and the text I have set in the response.

When ID is 857:
857

When ID is 854:
854 854_text

I am sure the record ID is being passed over to the query. I am not sure if the number variable is the same as the integer type in my database.
I am using SQL Server Express and using ASP.NET as the server model. My web server is IIS Express as my machine is a development workstation only.
I am able to insert/create records but haven’t tried updating any records yet.

I am not sure if this is a bug.
Please help.

Thanks in advance.

zitroware · July 6, 2019, 3:50am

Hello,

I am very confused. I am still trying to see what is going on. I tried to set the POST variable to number type and added validation rules. It went through but still cannot delete the record and still returning the same error trying to convert nvarchar value to type int.

I have also tried earlier to enter letters to check if the validation is working.

Has anyone encountered the same issue?

Thanks.

zitroware · July 6, 2019, 4:09am

I think it is a bug. I tried to recreate the query and still the same issue. But when I tried to create a custom query, it worked.

zitroware · July 11, 2019, 9:45pm

Hello,
Still getting the same error on another query.
Please see below for a screenshot of the error:

I don’t know if this is going to help, but here’s the complete StackTraceString:
StackTraceString.zip (727 Bytes)

brad · July 11, 2019, 10:35pm

Hi Joey,

Keep in mind I have not done any work with .net or SQL Express. But it seems by what I see that your primary key in your database is not an integer field? There should be no reason to convert it to an integer (That I can think of).

Dave · July 12, 2019, 12:34am

I know it doesn’t resolve the issue you are having but I pass a cookie rather than post the id, then match the cookie for deleting the record. Set an on click action to set the cookie using the Cookie Manager, name the cookie something simple like ‘delrecordid’ (important all lower case). Then on success remove the cookie and ping up a notification etc…

Very simple.

zitroware · July 12, 2019, 5:51pm

Hi @brad ,
It is an integer. What confuses me is if I use a custom delete query, it works.

Hi @Dave,
Any reason why you are using a cookie instead of passing the id?

Thank you.

Dave · July 12, 2019, 6:00pm

I just find it a lot quicker to do it this way. Also, I usually pass a unique identifier rather than a primary key so no room for anybody trying to inject a string, in either the post variable or cookie. I’ve always hated passing the record id myself, too easily interfered with and a common vector for inquisitive minds to play around with…

zitroware · July 12, 2019, 6:03pm

This is a good point @Dave. I may have to chat with you directly to ask for help on how to do it.

Dave · July 12, 2019, 6:04pm

Anytime @zitroware, just ping me a message and will get back to you. Have just got in so if I don’t reply immediately I will get back to you shortly.

brad · July 12, 2019, 6:05pm

Passing the ID should not be an issue unless you are passing it as a URL parameter that shows up in the address bar as far as I know. At least I have never found a way to compromise it.

zitroware · July 12, 2019, 6:06pm

Maybe you have a tutorial uploaded here so I can look into it later? And of course for everyone else to see and learn from too.

Dave · July 12, 2019, 6:08pm

Any string that is auto increment is incredibly vulnerable be it a post variable, cookie, or session, all can be manipulated. It is amazing the amount of Users who ‘play-around’ where they shouldn’t be.

I like to study all my server logs to improve security where I can, and it’s probably one of the most common entry points for the wannabe.

zitroware · July 12, 2019, 6:13pm

@brad, @Dave, the ID is sent via POST, it may not be seen in the URL bar of the browser but it still can be seen by someone who wants to “see” it. It is not encrypted even when using SSL. Am I correct or my understanding is skewed? Please correct me.

Dave · July 12, 2019, 6:31pm

A quick look at your browser console will reveal a whole world of exploitable information.

We do this, it can have a small effect on MYSQL performance but unless you have tens of thousands of records this is minuscule.

Have a field in your table called something like uniqueRef with a varchar value of say 64 (we prefer a nice long variable, this could be shorter or longer, not really important but ideally around 10 or more). On Insert select this field and for its value tap the dynamic lightening bolt and select timestamp, then press the magic wand button and right click on timestamp and select cryptographic and pick any of the algorythms, SHA256 for example, we just want some randomness to this data and timestamp is pretty unique as the chances of a collision are millions to one… For the Salt simply select the timestamp again and click select, and save your action. Now when a new record is inserted this value will be populated with a unique string. On the update/delete side simply add Cookie Manager to your page and add a cookie called uniqueref (ALL LOWER CASE). Then select your delete button/icon/image/row and add a Dynamic Event, select Mouse - Click. Open the Action and pick Set Cookie from the Cookie Manager, select the cookie you created, and from the Repeat or its details the uniqueRef field and set to Secure. Click Select, save the page. In your Server Action for Update/delete under Global - Cookies add your cookie name (ALL LOWER CASE), then simply filter this value against the cookie value for the records uniqueRef. Job’s a good-un!

Dave · July 12, 2019, 6:37pm

Just a tidy up extra. Once the action has run successfully you can set a dynamic event on success to remove the cookie ready for the next Action. Obviously another entry with the same reference is millions if not billions to one so it is not life and death important, just good house-keeping!

brad · July 12, 2019, 6:39pm

Very interesting, Dave. Thanks

Dave · July 12, 2019, 6:46pm

@brad, Every little helps dear sir. It is a simple solution to a common issue and Wappler makes it even easier than ever to take this precaution against a common threat. A simple script could wipe out all of your records in a worst case scenario, within seconds/minutes.

Hyperbytes · July 12, 2019, 6:50pm

As an extra I tend to always include the security provider in the delete actions where possible

In most cases where it is important, the user will be logged in with some sort of security.

I tend to also check the security.identity is set to stop people passing parameters from non authorised sources like other web site pages.

Where the data is Llnked to the user, i will add the security provider ID as part of the delete routine i.e the delete will be:

WHERE itemid = $_POST. itemid AND userid = security.identity

as an additional layer

Dave · July 12, 2019, 6:57pm

Am in-line with Brian 100%. Security is so often over-looked especially with regards to CRUD actions. Separating your database in to specific users (don’t use a single database User granted with all permissions if it is not necessary) is also a great way of protecting against such invasions. It can get complicated but if your data is worth it and your Client is important to you we must do all we can to protect their data.

We tend to have an administrator User (not named Administrator obviously) with the necessary permissions to perform CRUD operations, and a basic User for simple viewing of data without too many permissions to the database. Easily managed in the Database Connector and via any database management console/software.