Input string was not in a correct format fix

Question

1.00/5 (2 votes)

See more:

I know this may lead to sql injection but I just want to know how to fix this error.
I have a db with multiple columns, and I am using this code to insert to table:

ed.addproy("avance_presupuestario", "nombre,usuario,año,proyecto,porcentajeejecucionglobal,porcentajeejecucionpresupuestaria,fecha", name.Text + "','" + user.Text + "','"+yr.Text+"','" + proy.Text + "','" + Int32.Parse(tec.Text) + "','" + Int32.Parse(pre.Text) + "','" + date.Text);
        System.Threading.Thread.Sleep(10);
        //name.Text = "";
        //user.Text = "";
        //proy.Text = "";
        tec.Text = "";
        pre.Text = "";
        date.Text = "";

this is the edit class ("ed")

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for EditClass
/// </summary>
/// 
public class EditClass
{
    MySql.Data.MySqlClient.MySqlConnection conn;
    MySql.Data.MySqlClient.MySqlCommand cmd;
    MySql.Data.MySqlClient.MySqlDataReader reader;
    String queryStr;
    String proyecto = "";
    public static String proyecto2 = "";
    String cargo = "";
    String usuario = "";
    public EditClass()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public void addproy(String table, String columns, String data)
    {
        String connString = System.Configuration.ConfigurationManager.ConnectionStrings["web"].ToString();
        conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
        conn.Open();
        queryStr = "";
        queryStr = "INSERT INTO " + table + " (" + columns + ")" + "VALUES('" + data + "')";
        cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
        cmd.ExecuteReader();
        conn.Close();
        //id_area.Text = "";
    }
    public void deleteproy(String column, String table, String data)
    {
        String connString = System.Configuration.ConfigurationManager.ConnectionStrings["web"].ToString();
        conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
        conn.Open();
        queryStr = "";
        queryStr = "DELETE FROM "+table+" WHERE "+column+" like'" + data + "'";
        cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
        reader = cmd.ExecuteReader();
        while (reader.HasRows && reader.Read())
        {

        }
        reader.Close();
        conn.Close();
    }
}

Please help me fix this error.
Thanks.

What I have tried:

I have tried changing the types of the variables and even tried going back to an earlier version but had the same result.

Posted 19-Dec-17 13:21pm

Member 10850253

Updated 19-Dec-17 19:50pm

Add a Solution

Comments

RossMW 19-Dec-17 19:50pm

Check the table structure against what you are try to insert. Also you can copy the QueryStr text and run it directly against the database and expeiement to find out where it is going wrong. You also need to be careful with date formats as sql has a bad habit of defaulting back to american format.

Member 10850253 20-Dec-17 7:48am

I checked and the initial and closing tags are in the edit class, and the rest are in the first line of code. Do you have any other ideas to help me fix this. Thanks.

RossMW 20-Dec-17 13:47pm

Sorry, Not enough info to determine anymore options. Pacticularly around the database structure. It appears to be an incorrect sql statement construction (hence run it against the database directly to test). A lot of these issues can be solved by using parameters as highlighted by OriginalGriff. It a good practise to get into. Especially where the user may have any remote chance of an input into it.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2017-12-19T19:50:00

Never do 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:

SQL

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:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

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?

Fix that, and your conversion problem will probably go away at the same time.