2

I have used this string and tested it with string concatenation.But as you know it is not safe to use this to format an sql command.

 SqlCommand param = new SqlCommand();
        param.CommandText = "INSERT INTO Circle (Center_Point, Circle_Data) VALUES (geometry::STGeomFromText('POINT(@center_lat @center_lng)',0),geometry::STGeomFromText('POLYGON((@polygon))',0));";
        param.Parameters.Add(new SqlParameter("@center_lat", center_lat));
        param.Parameters.Add(new SqlParameter("@center_lng", center_lng));
        param.Parameters.Add(new SqlParameter("@polygon", polygon));

I go to parametrize the string and get the following error:

System.Data.SqlClient.SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24141: A number is expected at position 17 of the input. The input has @center_lat.

Looks like it hasn't put the value into the string. but when I step through the code it does indeed hold the value.

What could be the problem?

Thanks

SammyG
  • 299
  • 1
  • 4
  • 15
  • Everything between `'` is considered part of the string value, so the parameters won't be replaced by the given values. – Me.Name Jun 08 '15 at 09:04
  • Any suggestion on how to get round this?. Im trying ways now.. – SammyG Jun 08 '15 at 09:21
  • Not sure if there are smarter ways, but to use sql while keeping the parameters intact, you could do something like `... select 'POINT(' + cast(@center_lat as varchar) + ' ' + .....` (NB, The parameter type used determines the number of digits that will be included) – Me.Name Jun 08 '15 at 09:43
  • I can concatenate strings fine and it works, but isn't this considered unsafe from an SQL Injection point of view? this is why I am parametrizing it. – SammyG Jun 08 '15 at 10:09
  • Not if you concatenate the parameters instead of the values. I reckon the first version used the raw values, in other words the concatenating took place while creating the sql string. (e.g `" 'Point(" + center_lat`. By using `'POINT(' + @center_lat etc ` inside your string, you concatenate inside the sql command, still using the parameterized values. Note that as far as visual studio is concerned, you're still using one string value and don't concatenate anything at all. – Me.Name Jun 08 '15 at 11:07
  • Oh I see yea. however just tried it and its not working... the only way to get it to work is concatenation of the raw value :/... – SammyG Jun 08 '15 at 11:16
  • in desktop C# not asp which im using. You can use a handy `SqlGeometry` toparse the data and make it work as i have seen examples. But `SqlGeometry` isn't available in ASP. Is there any ways in which you can include it? – SammyG Jun 08 '15 at 11:18
  • Would it actually matter in this instance if I did concatenate the raw value as its within a data type declaration "geometry::STGeomFromText('POINT("DATA"))" so if it was open to sql injection, replacing the DATA with other sql code wouldn't work anyway right? – SammyG Jun 08 '15 at 11:34
  • Don't think it's native to desktop, but rather part of the assembly `Microsoft.SqlServer.Types` (available in CLR projects). You should be able to add a reference to it if you have the proper sql server components installed on your dev machine (as described here http://barendgehrels.blogspot.nl/2011/04/sqlgeometry-types-and-linq.html ). Haven't used it myself, so don't know how well it works deployed in Asp – Me.Name Jun 08 '15 at 11:36
  • If the raw value is an unchecked string, it could contain an end string character and it could be tried to properly end the open statements/function calls, to follow with their own commands. How big the risk is all depends. If you're using numeric values only, I'd say go for it ;) ( <- no official statement that :p ) But seriously, the (lack of) risk depends on several factors, such as how much the input can be manipulated/data type etc. Still, concatenating the strings inside the sql using parameters should work too. – Me.Name Jun 08 '15 at 11:39
  • PS, why didn't the concatenating with parameters work? Could you try `... = "INSERT INTO Circle (Center_Point, Circle_Data) VALUES (select geometry::STGeomFromText('POINT(' + @center_lat + ' ' + @center_lng+ ')',0),geometry::STGeomFromText('POLYGON(('+ @polygon + '))',0))"` – Me.Name Jun 08 '15 at 11:49
  • It just produced the same error. However thanks so much for linking me to the adding of the assembly I have finally got it to work using that. You are a star! thanks. if you would like to post that as an answer? and I can update my post with the new working code :) – SammyG Jun 08 '15 at 12:57
  • Do not put SOLVED in your tile, either delete your question or post an answer to the question and accept it. – Scott Chamberlain Jun 08 '15 at 13:07
  • Glad I could help, I can add an answer referring to the assembly, however your own amended code is far more useful for future readers. You could post that in an answer and accept it as such :) – Me.Name Jun 08 '15 at 13:24
  • Thanks again, I have just had a thought about the Polygon string. Although it is now parametrized in the query. The creation of that string is a for loop that goes through all of the points in an array and adds it (+=) to the end of a formatted string. This im guessing is still unsafe? – SammyG Jun 08 '15 at 13:33

2 Answers2

3

Thanks to Me.Name. I had to add the correct assemblies to the ASP.net project, which enabled me to set the UDT type correctly. Updated Code is below. 

SqlCommand param = new SqlCommand();
        SqlGeometry point = SqlGeometry.Point(center_lat,center_lng,0);
        SqlGeometry poly = SqlGeometry.STPolyFromText(new SqlChars(new SqlString(polygon)),0);
        param.CommandText = "INSERT INTO Circle (Center_Point, Circle_Data) VALUES (@point,@poly);";
        param.Parameters.Add(new SqlParameter("@point", SqlDbType.Udt));
        param.Parameters.Add(new SqlParameter("@poly", SqlDbType.Udt));
        param.Parameters["@point"].UdtTypeName = "geometry";
        param.Parameters["@poly"].UdtTypeName = "geometry";
        param.Parameters["@point"].Value = point;
        param.Parameters["@poly"].Value = poly;
Community
  • 1
  • 1
SammyG
  • 299
  • 1
  • 4
  • 15
0

For me, using MySQL geometry, I had to use the MySqlGeometry class rather than the SqlGeometry as in the answer from @SammyG

parameters.Add(new
{
   ...
   MyGeometryObject = MySqlGeometry.Parse($"POINT({point.WGS84Lon} {point.WGS84Lat})").Value,
});
Jack
  • 871
  • 1
  • 9
  • 17