Mysql queries to avoid SQL injection

Question

3.12/5 (3 votes)

See more:

following are some MySQL queries :

1]

C#

string sel=SELECT u.Id as ID,u.Name,u.IName,s.FId,s.TId,s.data,s.Date,s.ID as Id,s.iurl,s.Id as SpId,s.ITitle  FROM mydb.userdb as u,  mydb.psp as s WHERE u.Id=s.FId AND s.FId='" + Session["UId"].ToString() + "'order by s.Id DESC";

2]

C#

string postlob = "Insert into mydb.`dbo.sp`(ITitle,FId,TId,D,imageurl,SendDate,d,dn)SELECT ITitle,FromId,ToId,data,iurl,Date,0,0 from mydb.personalscrap where Id='" + test + "'";

3]

C#

string deletpass = "delete from  mydb.pesp where  Id='" + test + "'";

4]

C#

string postlob = " INSERT INTO mydb.sp2(ITitle, pbyid,`Name`, sid, data, fstatus, iurl, date, pid,d,dn)SELECT(select ITitle from mydb.psp where Id = '"+test+"' ) ,(select FId from mydb.psp where Id = '"+test+"'),(select `Name` from mydb.user where Id = '1'),FId,(select data from mydb.psp where Id = '"+test+"'),FStatus,(select iurl from mydb.psp where Id = '"+test+"'),(select date from mydb.psp where Id = '" + test + "'),(select max(Id) + 1 from mydb.sp2),0,0 from mydb.`dbo.tbl` where Id = '" + Session["UId"] + "'  AND FStatus = '1'";

5]

C#

string queryupdate = "Update  mydb.psp set data='" + sugg + "' where Id='" + test + "'";

please help me to make above queries sql injection free.

I go through some article and used them for insert queries but now when insert query nested with select query then how to perfoem parameterized query to avoid sql injection.

I also want to get know how to pass parameters here

C#

MySqlCommand cmd = connection.CreateCommand();
              cmd.CommandText = SelectQuery;
              MySqlDataAdapter ada = new MySqlDataAdapter(cmd);

              DataTable dtt = new DataTable();
              ada.Fill(dtt)
              return dtt;

Posted 29-Apr-16 4:34am

Kishor-KW

Updated 29-Apr-16 8:44am

v4

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Accepted Answer · 2016-04-29T04:47:00

Solution 1

I'll show you 1 example. The rest you should be able to do yourself:
Your query:

C#

string sel="SELECT u.Id as ID,u.Name,u.IName,s.FId,s.TId,s.data,s.Date,s.ID as Id,s.iurl,s.Id as SpId,s.ITitle  FROM mydb.userdb as u,  mydb.psp as s WHERE u.Id=s.FId AND s.FId='" + Session["UId"].ToString() + "'order by s.Id DESC";

Your querytext will be:

C#

string sel = "SELECT u.Id as ID,u.Name,u.IName,s.FId,s.TId,s.data,s.Date,s.ID as Id,s.iurl,s.Id as SpId,s.ITitle  FROM mydb.userdb as u, mydb.psp as s WHERE u.Id=s.FId AND s.FId=@FID order by s.Id DESC"

Your query is defined and can be used with your MySqlCommand and MySqlConnection.
So:

C#

void GetMyUserDetail(int ID)
{
    using (MySqlConnection conn = new MySqlConnection(connStr))
    {
        const string sel = "SELECT u.Id as ID,u.Name,u.IName,s.FId,s.TId,s.data,s.Date,s.ID as Id,s.iurl,s.Id as SpId,s.ITitle  FROM mydb.userdb as u, mydb.psp as s WHERE u.Id=s.FId AND s.FId=@FID order by s.Id DESC";
                
        using (MySqlCommand cmd = new MySqlCommand(sel, conn))
        {
            cmd.Parameters.AddWithValue("@FID", ID); // is parameter from method
            conn.Open();
            using (MySqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    // do something with your resultset
                }
            }
        }
    }
}

More information: MySQL :: MySQL Connector/Net Developer Guide :: 4.1.4 Working with Parameters[^] (which is 1 of 55,000 results Google gives you on this subject)

Posted 29-Apr-16 4:47am

Herman<T>.Instance

Updated 29-Apr-16 7:42am

v6

Comments

[no name] 29-Apr-16 10:59am

A 5 for this

Kishor-KW 29-Apr-16 12:04pm

thanx for the replay please help me in the query number 4. please give example of it

and also tell is this way is better or store procedure is better?

CHill60 29-Apr-16 12:22pm

That query needs some serious work - get rid of all of the sub-queries and use JOINs

Kishor-KW 29-Apr-16 12:40pm

ya i go for it but didn't found any useful link. it wolud be greatful if you mention anylink to refere

Richard Deeming 29-Apr-16 13:38pm

Without changing the query:

const string postlob = " INSERT INTO mydb.sp2(ITitle, pbyid,`Name`, sid, data, fstatus, iurl, date, pid,d,dn)SELECT(select ITitle from mydb.psp where Id = @test ) ,(select FId from mydb.psp where Id = @test),(select `Name` from mydb.user where Id = '1'),FId,(select data from mydb.psp where Id = @test),FStatus,(select iurl from mydb.psp where Id = @test),(select date from mydb.psp where Id = @test),(select max(Id) + 1 from mydb.sp2),0,0 from mydb.`dbo.tbl` where Id = @Uid  AND FStatus = '1'";

using (MySqlCommand cmd = new MySqlCommand(sel, conn))
{
   cmd.Parameters.AddWithValue("@test", test);
   cmd.Parameters.AddWithValue("@Uid", Session["UId"]);
...

The pattern is simple; anywhere you're using string concatenation (or string.Format, or string interpolation, etc.), replace it with a parameter name, and add the value you were concatenating to the command's Parameters collection with that name.

Ideally, you should be able to make the string you pass to the MySqlCommand constructir a const; it you can't, then you've probably missed something.

Kishor-KW 29-Apr-16 13:44pm

ok thanx

Matt T Heffron 29-Apr-16 13:26pm

(I just fixed a pretty obvious typo...)

Also, isn't the
conn.Close();
redundant since the conn is created in a using statement and would be closed when disposed at the end of the block?

Richard Deeming 29-Apr-16 13:44pm

You're missing a semi-colon from the string sel = "..." line. The MySqlDataReader should also be in a using block. And, as Matt said, the .Close() lines are redundant when the object is wrapped in a using block.

I've changed those for you. :)

Kishor-KW 29-Apr-16 13:44pm

if my query is select * from then there is no need of parameter am i right?

CHill60 29-Apr-16 20:02pm

Avoid using "Select *" and the parameter would probably be in the WHERE clause

Herman<T>.Instance 30-Apr-16 3:10am

The parameter is bound to your where clause, not your select part of the query. Select * asks the database to find out which columns are available. Select field1, field2, field3 tells the database which columns to select. For large amount of data in tables that performs better.

CHill60 · Accepted Answer · 2016-04-29T04:53:00

In addition to the solution from digimanus above also consider using Stored Procedures - MySQL :: MySQL Connector/Net Developer Guide :: 4.1.5 Working with Stored Procedures[^]. You would still pass parameters.

See this article for an example Working C# code for MySql Stored Procedures IN, OUT, and INOUT parameters[^]

And also consider the dangers of dynamic sql by reading this article too How to prevent SQL Injection in Stored Procedures[^]

Patrice T · Accepted Answer · 2016-04-29T05:47:00

Every time you dynamically build an SQL query with parameters you don't control, you are at risk of SQL injection.
Anything like

C#

Cmd= "SQL commend with filter='"+parameter+"'";

is supposed to give

SQL

SQL commend with filter='parameter_value'

but a malicious parameter can lead to something completely different.
for exemple, if you have

C#

parameter="a';\nMalicious SQL commends;\ndummy SQL with filter='a'";

you end up with

SQL

SQL commend with filter='a';
Malicious SQL commends;
dummy SQL with filter='a'

And SQL commend have been injected from client side.

this is only a demo of principle, details will be different.

SQL injection - Wikipedia, the free encyclopedia[^]
SQL Injection[^]

To downvoter, feel free to tell me what is wrong here.

Mysql queries to avoid SQL injection

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0