0

The following code uses a text file as an input to a local SQL database by acting as if the text file is a table. But the problem here is that it is buggy given that using SQLEXecute does not parse the data at all. This means that the table will not be created even if we use an actual text.

 public void ParseRoutingFile()
    {
        using (StreamReader sr = new StreamReader(Engine.LIST_FILE_PATH + @"\ROUTING.TXT", Encoding.Default))
        {
            while (sr.Peek() >= 0)
            {
                string row = sr.ReadLine();
                if (row != null)
                {
                    string[] parts = row.Split(';');
                    if (parts.Length > 0)
                    {
                        // find records for the specific terminal
                        if (parts[0] == Engine.TerminalId)
                        {
                            string query = string.Format("INSERT INTO Routing (RId, Weeknum, Year, Days) " +
                                    "VALUES ('{0}','{1}','{2}','{3}')",
                                    parts[4].Trim(),
                                    parts[1].Trim(),
                                    parts[2].Trim(),
                                    parts[3]

                                );

                            Engine.SqlExecute(query);
                        }
                        else continue;
                    }
                }
            }
        }
    }

At this point I just want to read the file without the fear of falling.

Now, the table should look like this for the user

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-0pky"></th>
    <th class="tg-0pky">RID</th>
    <th class="tg-0pky">Weeknum</th>
    <th class="tg-0pky">Year</th>
    <th class="tg-0pky">Days</th>
  </tr>
  <tr>
    <td class="tg-0pky">□</td>
    <td class="tg-0pky">11</td>
    <td class="tg-0pky">45</td>
    <td class="tg-0pky">2019</td>
    <td class="tg-0pky">4</td>
  </tr>
</table>
Jason Todd
  • 3
  • 5
  • If I send you a file that contains `'); DROP TABLE Routing; --` in the any of the columns, you'll have a *very* bad day. – vzwick Dec 04 '18 at 12:49
  • @vzwick that is why I posted here, because I don't know a better way to write that – Jason Todd Dec 04 '18 at 12:52
  • https://stackoverflow.com/questions/21709305/how-to-directly-execute-sql-query-in-c-have-example-batch-file - note the `command.Parameters.AddWithValue(...` ;) – vzwick Dec 04 '18 at 12:56
  • @vzwick So this wouldn't work? https://stackoverflow.com/questions/34691471/parse-object-from-sql because I wanted to parse my data – Jason Todd Dec 04 '18 at 12:58
  • It *works*. It's just super insecure and will be an absolute maintenance nightmare down the line. I would certainly suggest you look into an ORM such as Entity Framework (very powerful but takes a while to get a grasp of) or [Dapper](https://github.com/StackExchange/Dapper) (which might be better suited to your use case). – vzwick Dec 04 '18 at 13:05

2 Answers2

1

Your query is still incorrect. There are 4 columns specified and 5 values.

Here's what it looks like with the 4 columns and 4 values:

string query = string.Format("INSERT INTO Routing (RId, Weeknum, Year, Days) " +
                                "VALUES ('{0}','{1}','{2}','{3}')",
                                parts[4].Trim(),
                                parts[1].Trim(),
                                parts[2].Trim(),
                                parts[3]

As the others mentioned, you should look into parameterised queries, or at least escaping apostrophes to prevent SQL injection.

parts[4].Trim().Replace("'", "''"),

Or, all 4 fields sound like they should be numbers, so an alternative would be to remove the quotes and check that the values are valid numbers.

Robin Bennett
  • 3,192
  • 1
  • 8
  • 18
Rossco
  • 26
  • 2
  • Humblest apologies, not intentional. I realised my error too late & I could not delete the answer. I have accepted the edit by Robin Bennett. – Rossco Dec 04 '18 at 14:38
  • @Rossco As for my mistake, I modified the code to create a more generic model but because it used more objects I decided to remove them. As for the code itself I want to parse the values of my text, which are separated by a delimiter, to my database. If you have any trouble with understanding that, feel free to comment so I can make my goal more clear. – Jason Todd Dec 04 '18 at 14:39
  • Have you made any amendments as suggested above by Robin Bennett regarding the sql query statement and the mismatch between the number of columns specified and the number of values provided? Is it still not working then? I would next be asking what the value of Engine.TerminalId is in if (parts[0] == Engine.TerminalId) – Rossco Dec 04 '18 at 15:09
0

Your Query is Wrong

INSERT INTO Routing (RId, Weeknum, Year, Days) " + "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}', '{11}')

Notice 

(RId, Weeknum, Year, Days)

AND

('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}', '{11}')

You are saying you'll give 4 columns and then giving 12 columns

Mihir Dave
  • 3,954
  • 1
  • 12
  • 28