Length of string in String.Format()

Question

It's may be I'm doing something wrong but don't know why I'm getting such issue.
I'm using string.Format which took around 130 parameters my code look like as below

string query = string.Format(@"Insert into TB_LN_CASES (
                col1, 
                col2,
                col3,
                col4,
                ...
                ...
                col129,
                col130) Values ({0},{1},{2},{3}.....{129})", 
                col1.ToString(),
                col2.ToString(),
                col3.ToString(),
                col4.ToString(),
                ...
                ...
                col130.ToString());

The output which I'm getting in string is like

Insert into TB_LN_CASES (col1, col2,col3,col4,
                    ...
                    ...col129,col130) Values (abc,efd,gr,y,t,ui,u,re,re

String is incomplete, don't know what is the reason behind this or is there any alternative to do this, please suggest

Is there any max length constraint for string.Format ?

You should stop coding like this. You've got a list of columns so implement a list of columns and not variables en mass. — Quality Catalyst, May 05 '16 at 05:14
if column one is string type then u have to use the parameter as '{0}' instead of {0} . if this is numeric type then you can leave it. Take care of data type it should work. — Kiran Paul, May 05 '16 at 05:16
@QualityCatalyst could you please share something any link or code how to do it — Amit Bisht, May 05 '16 at 05:20
@NazmulHasan hey I'm getting some issue ok, don't judge it with my reputation — Amit Bisht, May 05 '16 at 05:26
**You are most definitely doing something wrong.** The thing you are doing wrong is using `string.Format` to create an insert statement, instead of using parameterized statements. This is a **major security hazard** since it's an open door for [sql injection attacks](https://en.wikipedia.org/wiki/SQL_injection) — Zohar Peled, May 05 '16 at 05:26
@ZoharPeled ok i'll keep it in mind, but the actual question is, is there any constrain with string.Format() . any max length constrain — Amit Bisht, May 05 '16 at 05:29
There is no MaxLength issue for you with the string. You run in an issue with your database first as it won't take statement millions of characters long. — Quality Catalyst, May 05 '16 at 05:32
Don't keep that in mind, change your code **now.** Sql injection attacks are very powerful and immunizing your code is very easy. All you have to do is use parameters instead of concatenating strings. — Zohar Peled, May 05 '16 at 05:33
btw, `string.format` already calls `ToString()` on every parameter it gets, so that's redundant. — Zohar Peled, May 05 '16 at 05:35
@Co.Aden: check the MSDN please (https://msdn.microsoft.com/en-us/library/system.string.format(v=vs.110).aspx). You are limited by the length of `string`. Again, super long statements won't be executable in your database anyway! — Quality Catalyst, May 05 '16 at 05:36
http://stackoverflow.com/questions/561020/string-format-parameters — monstertjie_za, May 05 '16 at 05:36
@QualityCatalyst this is really helpful, also I'll do use parameters instead of string, thanks — Amit Bisht, May 05 '16 at 05:38
@Co.Aden I have updated my answer to include the following link, hope this helps you. http://stackoverflow.com/questions/561020/string-format-parameters — monstertjie_za, May 05 '16 at 05:44
Be aware that contrary o what people like Zohar say there are good reasons to avoid parameters on insert statements. The main one: Insert can insert many rows.... but you are still limited to around 1000 parameters. Less than 10 lines in your example, while the insert statement could have 1000 rows in one statement or more. SQL injection is a total non issue with a little planning on the parameter processing. I am generally in favor of using parameters, but contrary to certain near religious zealot attitudes, one has to accept that there are cases it makes sense not to use them. — TomTom, May 05 '16 at 05:45
@TomTom inserting multiple rows can easily be achieved with a single table valued parameter, so that's a non-issue. any other reason not to use parameters? — Zohar Peled, May 05 '16 at 06:25
The argument against parameters in general is speed achieved across large datasets in loops. Depending on the source of the data, if one can rule out sql injection, then bulk inserts are vastly superior. — Drew, May 05 '16 at 06:29
Yes, bulk inserts are the preferred way to insert massive amount of data to sql server, however that's clearly not relevant when taking user inputs, so I don't really consider them as an argument against parameterized queries. — Zohar Peled, May 05 '16 at 06:51

TomTom · Accepted Answer · 2016-05-05T05:42:29.117

That is so totally not a string.format issue that it is not funny.

Please consider doing a little basic debugging yourself.

Values (abc,efd,gr,y,t,ui,u,re,re

This is not valid SQL. See, string values have to be in paranthesis of some sort ('abc' instad of abc).

Simply speaking your (btw, the old string.format syntax is hard to read - learn to use $"" strings, the new syntax for formatting in .NET 6.0) generated SQL is bad and you never considered this a SQL error.

Now, for the length issue - that is no, there is no sensible limit that you would reach. There is one, but it is LONG (not sure about the string limit - 2 gigabytes RAM?). It is likely you have a serious presentation issue (as in: The string is there, you just do not see it, like in the debugger, which may limit the output length).

I would reformat that to use he new $"{paramname}" syntax - it is a LOT easier to debug once you hit 10 or 20 parameters.

Please also note: The ToString calls on all those parameters are surplus (default call anyway).

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

If you are building up a SQL query, I would strongly recommend you use parameters in your query.

Here is an example:

string strQuery = "Insert into TB_LN_CASES (col1, ...) VALUES (@columnOneVariable, ...)";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.AddWithValue("@columnOneVariable", "yourValue");

At the moment you are very vulnerable to SQL injection.

To answer your question about the limit on String.Format(), please read here

edited May 23 '17 at 11:52

Community

1
1

answered May 05 '16 at 05:30

monstertjie_za

7,277
8
42
73

ok, i'll do that but main thing is, is their any max length constrain with string.Format() – Amit Bisht May 05 '16 at 05:31
Not that I am aware of. Take a read here, might be useful. http://stackoverflow.com/questions/140468/what-is-the-maximum-possible-length-of-a-net-string – monstertjie_za May 05 '16 at 05:34
Actually there is - but it will not bite you. I think you are limited to around 1 billion characters. Things get ugly way before that, though - and in terms of SQL... that would really blow anything sensible out of the water as one statement anyway. – TomTom May 05 '16 at 05:39
@TomTom Thanks for the info. Am I correct in saying, the limit, for in memory objects/variables, is 2GB, as per the article I have suggested above? – monstertjie_za May 05 '16 at 05:41
1

I think yes. For strings (remember, 2 bytes per char) that runs around 1 billion chars. Now, I have seen SQL Statements I Would assume to be longer than what most people here see (including multi megabyte insert statements).... but from there to the string limit.... worlds of difference. Also note: for the pure string part, consider using a stringbuilder (and it's AppendFormat method) as it may be more efficient internally. Heavy string manipulation with strings generally creates a ton of garbage (though I am not sure about a single string.format). – TomTom May 05 '16 at 05:47
@TomTom thanks for your input. Do you think StringBuilder would be more efficient in this case, where the OP has mentioned 130 parameters? – monstertjie_za May 05 '16 at 05:53
Not sure. Generally the advice is to use stringbuilder instead of a lot of additions - but one "format to rule them all".... never tried. I have no real idea how string.format works internally. The source is open source, jsut never looked it up. – TomTom May 05 '16 at 06:20
Me neither, something to check out later when I have a free moment. Thanks for the discussion. – monstertjie_za May 05 '16 at 06:23
How is stringbuilder protecting against sql injection? – Drew May 05 '16 at 06:27
@Drew I never mentioned StringBuilder will protect against SQL Injection – monstertjie_za May 05 '16 at 06:29
I know sir. Just wanted to make it clear to the OP. Thanks for helping him – Drew May 05 '16 at 14:00

score 0 · Answer 3 · answered May 05 '16 at 05:25

0

try this:

using (SqlConnection connection = new SqlConnection(this.connectionString))
{
    connection.Open();
    SqlCommand cmd = connection.CreateCommand();
    cmd.CommandText = @"Insert into TB_LN_CASES (col1, col2,col3, ..) 
                        Values (@value1, @value2, @value3, ..) ";
    cmd.Parameters.Add(new SqlParameter("@value1", value1));
    cmd.Parameters.Add(new SqlParameter("@value2", value2));
    cmd.Parameters.Add(new SqlParameter("@value3", value3));

    cmd.ExecuteNonQuery();
}

answered May 05 '16 at 05:25

SᴇM

7,024
3
24
41

I will -1 this because there are good reasons not to use parameters in this - mostly that you can make a LOT of rows in one SQL insert but are limited to around 1000 parameters. Also it does work around the question and never even bothers to answer it (which is a pure sql issue). – TomTom May 05 '16 at 05:38

Noni · Answer 4 · 2016-05-05T05:53:50.320

-2

string.Format("@"Insert into TB_LN_CASES{0},{1},{2}", col0,col1);

edited May 05 '16 at 05:53

answered May 05 '16 at 05:26

Noni

369
2
14

what my question is, is there any constrain with string.Format() I don't want to correct my query by any other way – Amit Bisht May 05 '16 at 05:27
Check your answer. It doesn't address the question and fails anyway as the built statement is non-SQL compliant. – Quality Catalyst May 05 '16 at 05:40

Length of string in String.Format()

4 Answers4