[Mono-list] Re: magic quotes (like PHP) (or prevent SQL Injection)

[James Grant]

Ahh, indeed - the code in question is using a string concatenation.

                        request = "public.\"InsertSchool\"( '" +
                                Name.Replace("'","\\'") + "', '" +
                                Address1.Replace("'","\\'")  + "', '" + .....


or at least, _some_ of it is using string concatenations - some is using (what 
might be) correct parameterization?


                        NpgsqlConnection npgsqlConnection = new 
NpgsqlConnection(cstr);
                        NpgsqlCommand command = new NpgsqlCommand("public.
\"InsertRecord\"(:a,:b,:c, :d,:f)", npgsqlConnection);
                                
                        command.Parameters.Add(new 
NpgsqlParameter("a",DbType.Int32));
                        command.Parameters.Add(new 
NpgsqlParameter("b",DbType.String));
                        command.Parameters.Add(new 
NpgsqlParameter("c",DbType.Int32));
                        command.Parameters.Add(new 
NpgsqlParameter("d",DbType.Int32));
                        command.Parameters.Add(new 
NpgsqlParameter("f",DbType.String));
                                
                        command.Parameters[0].Value = UserID;
                        command.Parameters[1].Value = Password;
                        command.Parameters[2].Value = ExtraID;
                        command.Parameters[3].Value = Year;
                        command.Parameters[4].Value = Title;
                                
                        command.CommandType = CommandType.StoredProcedure;
                        npgsqlConnection.Open();
                        try     
                        {
                                Object o = command.ExecuteScalar();
                                nReturn = Int32.Parse(o.ToString());
                        }

So I guess this code calling the stored procedure would work properly without 
having Title.Replace("'","\\'") .  Is this how it should all be done?  Its 
curious why some parts of the code do it this way, and others do it the other 
way... either there's a sound reason behind using both methods in different 
places, or pure randomness....

(its not my code!)

Thanks for your response!

Cheers,
  James


On Friday 08 April 2005 4:00 pm, Richard Norman wrote:
> I am not sure if this is database specific, but to avoid SQL Injection
> attacks, what should be going on in all queries (but it is developer's
> choice today) is that you use parameters in the query.
>
> Either parameterized queries or stored procedures. We mostly use SQL server
> so I am not sure how other DBMS handle this, but in SQL server when you use
> parameters, the "special" characters are automatically escaped properly.
>
> If you are using a concat string, then you could be vulnerable to this
> attack. Microsoft has been talking about it a lot in their security talks
> recently.
>
> Something like the following (using C#) is better.
> *******************************************
> System.Data.SqlClient.SqlCommand test1;
> test1 = new System.Data.SqlClient.SqlCommand("Select name from table where
> [ID]=@tempID"); test1.CommandType = System.Data.CommandType.Text;
> System.Data.SqlClient.SqlParameter para1 = new
> System.Data.SqlClient.SqlParameter("@tempID",
> System.Data.SqlDbType.VarChar, 50); test1.Parameters.Add(para1);
> *******************************************
>
> But that is what I recomend if the system supports it. And if not, Scrub
> the data REALLY well.. ;-)
>
> Richard Norman
> Web & Application Developer
> http://www.jazzynupe.net/Community/blog/
>
> Refs:
> http://search.microsoft.com/search/results.aspx?qu=SQL+Injection&View=msdn&
>st=b&c=0&s=1&swc=0
>
> http://toolbar.search.msn.com/results.aspx?FORM=DESKBR&q=SQL+Injection
>
> >>> mono-list-request@lists.ximian.com 4/7/2005 6:22:12 PM >>>
>
> Message: 1
> From: James Grant <topace@lightbox.org>
> Organization: Lightbox Technologies Inc
> To: mono-list@lists.ximian.com
> Date: Thu, 7 Apr 2005 12:06:36 -0400
> Subject: [Mono-list] magic quotes (like PHP)
>
> Hi there,
> I'm running into an issue with an application that has been ported over
> from windows to linux/mono.  On windows, you could enter apostrophe's in
> input boxes which are then stored in the database without any problems, but
> on mono it seems that the apostrophe's all need to be manually escaped on
> every query.
>
> I know PHP has an option for "magic quotes"
>     http://ca.php.net/manual/en/ref.info.php#ini.magic-quotes-runtime
>
> and was wondering if mono had a similar configuration option?
>
> Thanks,
>   James

-- 
James Grant
Lightbox Technologies Inc.
http://www.lightbox.org
613-294-3670

-- 
James Grant
Lightbox Technologies Inc.
http://www.lightbox.org
613-294-3670