-1

I want to take some long, messed up string and basically dump it in a string database field. The problem is I get all kinds of problems with the text messing up the insert query. Is there a way I can just ignore the contents and write the whole text without worrying about what's in it?

string connectionstring = @"connectionstring omitted"; // Omitted
using (SqlConnection sqlConnection = new SqlConnection(connectionstring))
{
    try
    {
        sqlConnection.Open();

        string s = " This is an email
           It has very  bad spacing and other special characters such as '' and (), it includes contact info like:

           John@john.com
           Tel :Office +(27) 082345674 3435667 / Mobile +(23)83562 4326556423
           Email: john@john123.com
           More complex numbers (1234) (122445123) ";

        SqlCommand command = new SqlCommand();
        command.CommandType = CommandType.Text;
        command.Connection = sqlConnection;
        command.CommandText = "INSERT INTO [dbo].[Emails] ([EmailText]) VALUES('" + s + "')";

        command.ExecuteNonQuery();
    }
    catch(Exception e)
    {
        throw e;
    }
}

How can I save this text without the query having a problem with it?

This is the error:

System.Data.SqlClient.SqlException: 'Incorrect syntax near 've'.
An expression of non-boolean type specified in a context where a condition is expected, near 'are'.
An expression of non-boolean type specified in a context where a condition is expected, near 'have'.
Incorrect syntax near 'e'.
Incorrect syntax near 'required'.
Unclosed quotation mark after the character string ')'.'

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
KnllBst
  • 121
  • 1
  • 9
  • 3
    You need to post a [mcve] if you expect us to be able to help you. I'm pretty sure you are concatenating strings rather than using parameters though – Camilo Terevinto Apr 30 '18 at 12:18
  • You need to share the code and also the input string which is messy? Also what kind of errors you are getting? – Chetan Apr 30 '18 at 12:20
  • Sorry I will edit the question to be more clear and descriptive – KnllBst Apr 30 '18 at 12:22
  • 1
    This is a fine question. OP doesn't _know_ how to write the correct code. – ispiro Apr 30 '18 at 12:22
  • 4
    @ispiro No, it's not, "i get all kinds of problems with the text messing up the insert query". We can only assume what's happening without seeing the code – Camilo Terevinto Apr 30 '18 at 12:28
  • 1
    @CamiloTerevinto Do you really have a doubt that OP is not using parameters?? – ispiro Apr 30 '18 at 12:29
  • 1
    There could be several approaches that you may follow: 1. If you will go with string then use parameterized queries. However depending on the text to be persisted, this may not save you from sql errors. 2. Convert your string to byte array as in https://stackoverflow.com/questions/16072709/converting-string-to-byte-array-in-c-sharp and then store bytes or blobs. 3) If this is a computer that you have access, save the strings into text files with guid names and save the path to the file in the database. – mcy Apr 30 '18 at 12:40
  • 1
    This is not a duplicate. Notice the difference. How do I escape ALL CHARACTERS, vs how do I escape A character. By the way the answer is to use a stored procedure, or use STRING_ESCAPE(@yourString,'json') from transact-sql. – Tyler S. Loeper Apr 30 '18 at 12:52

1 Answers1

1

You use a Parameterized Query. This will prevent attacks such as mentioned in this answer.

This answer has example code.

As suggested in a comment, here is a link to the docs of SqlCommand.Parameters .

Here's a general idea of the code:

string command = "SOMESQL @someId SOMEMORESQL";
SqlCommand sqlCommand = new SqlCommand(command, connection);
sqlCommand.Parameters.AddWithValue("@someId", theData);

I suggest you take a look at this answer as to the differences between using Add and AddWithValue. Both might have nasty surprises. (The first when using int which is 0 - see the link, the second when the system doesn't know what exactly you meant to do - see this link, and this one.)

ispiro
  • 26,556
  • 38
  • 136
  • 291
  • 2
    @downvoters care to explain why? Obviously OP doesn't know of them and is trying to manually sanitize input. They did the correct thing and asked a question here, realizing there must be a better way. – ispiro Apr 30 '18 at 12:23
  • Given the code sample, perhaps https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx may have been a better link? – Nigel Whatling Apr 30 '18 at 12:48
  • @NigelWhatling Feel free to edit that into my answer. – ispiro Apr 30 '18 at 12:56
  • Thanks this is what i was looking for, it solved my problem also sorry for bad question there is some info in the string that i cant reveal for obvious reasons. – KnllBst Apr 30 '18 at 13:03
  • 1
    @KnllBst Your question was fine to begin with. This site has become quite trigger-happy. As for confidential information - this is an even worse problem than it seems at first - if you then decide to remove information - it's still visible in the question's history (click on the 'edited .. minutes ago' link to see what I mean.) and even if you delete the post, it's still visible (though not searchable) to us with higher than 10k rep! Besides, there are sites that scrape information from StackOverflow... – ispiro Apr 30 '18 at 13:08