Inserting ' into mysql table

Question

1.00/5 (1 vote)

See more:

I want to insert student'coe into collegename field of table in mysql but because of
(')it is giving error to avoid it i found that mysql_real_escape_string should be used for but not getting proper syntax of it please help for proper mysql query syntax for it to inserting

JavaScript

student'coe

vaule into the table

collegename='mysql_real_escape_string(student'sCOE)'

What I have tried:

I want to insert student'coe into collegename field of table in mysql but because of
(')it is giving error to avoid it i found that mysql_real_escape_string should be used for but not getting proper syntax of it please help for proper mysql query syntax for it to inserting

JavaScript

student'coe

vaule into the table

collegename='mysql_real_escape_string(student'sCOE)'

Posted 15-Apr-16 2:29am

Kishor-KW

Updated 15-Apr-16 3:20am

Add a Solution

1 solution

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)

Richard Deeming · Accepted Answer · 2016-04-15T03:20:00

Solution 1

The problem is that you are using string concatenation to build your queries. That leaves your code vulnerable to SQL Injection[^].

You need to change your code to use parameterized queries instead. That means switching from the mysql_ methods to either MySQLi[^] or PDO[^].

This SO answer[^] has a pretty good explanation.

EDIT: Turns out you're using C#, even though your question refers to a PHP function. Parameterized queries in .NET are simple:

C#

using (var connection = new MySqlConnection("..."))
using (var command = new MySqlCommand("INSERT INTO YourTable (Column) VALUES (@Column)"))
{
    command.Parameters.AddWithValue("@Column", "student'coe");
    
    connection.Open();
    command.ExecuteNonQuery();
}

Alternatively, use Dapper[^].

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Posted 15-Apr-16 3:20am

Richard Deeming

Updated 15-Apr-16 10:04am

v2

Comments

Kishor-KW 15-Apr-16 9:25am

please give one example for query contain ' (apestop)

Richard Deeming 15-Apr-16 9:27am

The StackOverflow answer I linked to has two examples of property parameterized queries.

Kishor-KW 15-Apr-16 9:32am

sql injection occured when we mention control directly into the qurey e.g textbox control. but I am taking values into the variable and then mentaion it into the query.

and ' (apestop) is not accepted by mysql. then how can parameterize query solve the problem?

we have to use \ or something like mysql_real_escape_string but i don't know what exactly to use.

Richard Deeming 15-Apr-16 9:36am

Well, you clearly don't understand SQL Injection!

If you take the control's value, copy it into a variable, and then concatenate that variable into the query, your code is still vulnerable. Copying the value into a variable doesn't magically make the vulnerability go away.

Using a parameterized query ensures that the data cannot be confused with the commands.

Seriously, read at least one of the links I gave you!

Kishor-KW 15-Apr-16 9:37am

ok I will. but please tell me will it solved the problem of '(apestop)

Richard Deeming 15-Apr-16 9:39am

Yes, using a parameterized query will solve the problem of parameter values containing apostrophes. :)

Kishor-KW 15-Apr-16 9:44am

Ok thank you. :)

CPallini 15-Apr-16 10:29am

My 5.

Kishor-KW 15-Apr-16 15:46pm

I am using separate class file for insert update all database related query,where I just pass the query. so that i wouldn't be a need to use Mysqlconnection on ervery page where need.but now as all query having different number of parameters in parameterized query what should I have to do to follow same approach

Richard Deeming 15-Apr-16 15:48pm

Update your query method(s) to accept an array of parameter values. Loop through the array and call the relevant method to bind the parameter to the query.

Kishor-KW 15-Apr-16 15:52pm

please give any example or link to refer, is it good to use array?

Richard Deeming 15-Apr-16 15:55pm

PHP: Arrays[^]
PHP: Function arguments[^]

Kishor-KW 15-Apr-16 15:58pm

i am using c# :)

Richard Deeming 15-Apr-16 16:00pm

What?! But your question explicitly said mysql_real_escape_string, which is a PHP function!

Richard Deeming 15-Apr-16 16:10pm

In C#, something like this will work:

public void ExecuteNonQuery(string commandText, CommandType commandType, params MySqlParameter[] parameters)
{
   using (var connection = CreateConnection())
   using (var command = connection.CreateCommand())
   {
      command.CommandText = commandText;
      command.CommandType = commandType;

      if (parameters != null)
      {
         command.Parameters.AddRange(parameters);
      }

      connection.Open();
      command.ExecuteNonQuery();
   }
}
...
yourClass.ExecuteNonQuery("INSERT INTO YourTable (YourColumn) VALUES (@YourColumn)",
   new MySqlParameter("@YourColumn", "student'coe"));

Matt T Heffron 15-Apr-16 16:33pm

+5 for the answer (and for patience!)