0

I have a simple select query in a verbatim string in C#/.net core 2.1 that looks somewhat like this

public const string QUERY_HTML = @"SELECT col1, col2 from table1 JOIN table2 ON table1.id = table2.id WHERE data LIKE '%<span class=''myclass-ident''%' ORDER BY col1";

EDIT: Per commenters suggestion, I ran the profiler and the the query in the profiler is the same as the one in the code and the text visualizer in debug. In other words, the genericized query above does seem to be the one that gets run, at least that's what the profiler says.

The actual query isn't important - I genericized the column names, but its the same otherwise. If I run it in SQL Server Management Studio, it works just fine.

But when my code runs the query, it clobbers the database. Meaning the database is completely barebones. All users, tables, stored procedures and data (obviously if there are no tables) are gone.

If I run the query without the @ sign it works just fine.

I couldn't believe it at first - I thought there must be something else going on. I restored my local database and ran it again with the @ sign and it again reset the database to a bare/skeleton state. I also tried it on a different machine.

Obviously, I know to not use the @ sign in front of the query. I originally had it in there to escape some characters that have since been removed, so I no longer need the @ but it still leaves the question as to why would the @ cause the database to be clobbered?

SQL Server version is 2012 (v11.0.7001.0).

If it helps, here is the code that runs the query

using (var conn = new SqlConnection(Constants.LOCAL))
{
    using (var cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = Constants.QUERY_HTML;

        conn.Open();

        DataTable dt = new DataTable();

        try
        {
            using (var reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        if (dt.Rows.Count > 0)
        {
            result.payload = dt;
            result.hasPayload = true;
            result.code = 200;
        }

        return result;
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Taylor Ackley
  • 1,397
  • 1
  • 16
  • 31
  • 2
    you have columns named `col1` and `col2` in your database? I think you might of edited the actual code you ran and taken out the problem part. You say the actual query is not important -- it actually is. What is the actual query? – Hogan May 30 '19 at 16:38
  • 1
    @Hogan I'm guessing they changed the column names for SO's sake... – Dortimer May 30 '19 at 16:39
  • @Dortimer I don't know what SO means in this context but if what happened is as described then the contents of the query are important. – Hogan May 30 '19 at 16:41
  • 1
    SO means StackOverflow most likely – Vidmantas Blazevicius May 30 '19 at 16:42
  • I changed the column names around, yes. Outside of that, the query is the same. – Taylor Ackley May 30 '19 at 16:42
  • 4
    Profile the sql server and check what query is executed when you run it from code – Vidmantas Blazevicius May 30 '19 at 16:43
  • When you say the database is empty after the query is run, do you mean it's empty in whatever client you used before to run the query manually or it appears empty to your program but not to the other client? In the latter case double check your program connects to the right database. – sticky bit May 30 '19 at 16:47
  • @stickybit When I say it clobbers the database, it actually clobbers the database. If I look at the database in SSBMS before running the query, I see 50+ tables, 200+ sprocs, 5 users. If I look at it after the query, it only has system tables, system users, system sprocs, etc. – Taylor Ackley May 30 '19 at 16:49
  • @VidmantasBlazevicius - I tried your suggestion - and the query was identical. I updated the answer. I will say that the profiler highlighted this section as red `'% – Taylor Ackley May 30 '19 at 17:06
  • 3
    From your description, this makes no sense at all. The `@` string prefix is a compile-time thing - it has no influence on the generated/executed code (in this case). I would suggest that your SQL Server instance is corrupt, somehow. – 500 - Internal Server Error May 30 '19 at 17:20
  • @500-InternalServerError You're right that it makes no sense. I tried it on a different environment/server and got the same result, so both would have to be somehow corrupt. – Taylor Ackley May 30 '19 at 17:28
  • 2
    Does it happen with just e.g. `@"select * from table1"` too? – 500 - Internal Server Error May 30 '19 at 17:33
  • There has got to be something else going on. Is that your full program? What other SQL stuff do you have going on if not? Are there any stored procedures or triggers on the DB that could be causing this? – MrZander May 30 '19 at 18:11
  • @MrZander There is some post-processing of the data for analysis, but its all done in C#. As far as SQL and query code, that's all of it. After seeing the comments, I too think it might be something else. But im not sure what would clobber the db like that. – Taylor Ackley May 30 '19 at 18:20
  • I upgraded Sql Server from 11 to 13 and changed to integrated security now it doesn't happen anymore with or without the @. – Taylor Ackley May 30 '19 at 20:39
  • 2
    Try this... create a user in the database that only has read permissions. Update your connection string in the C# code to execute the query as that user. SQL Server will not let the restricted user account drop SQL objects, so you should get some kind of error message. – DeadZone May 30 '19 at 20:57
  • Perhaps you are running into [this problem](https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails). You don't provide any context about how your run your code so guessing. – SMor May 31 '19 at 15:16

0 Answers0