Jump to content

SQL insert into with mysql_real_escape_string


a.mlw.walker

Recommended Posts

I am trying to insert variables into a database, some of which potentially could hold apostrophes.

I know about mysql_real_escape_string so I generate this sql statement:

$sql = ("INSERT INTO $fcbk_id (PredictedAt, HomeTeam, AwayTeam, HomePrediction, AwayPrediction, GameKey)
VALUES ('".mysql_real_escape_string($date_now)."', '".mysql_real_escape_string($HomeTeam)."', 
'".mysql_real_escape_string($AwayTeam)."', '".mysql_real_escape_string($HomeScore)."', 
'".mysql_real_escape_string($AwayScore)."', '".mysql_real_escape_string($GameKey)."')");

It dont get any errors, even with this:

if (!mysql_query($sql))
     {
       // SQL failed, print error message and abort
       print mysql_error();exit();
     }

however it still doesnt insert anything after an apostrophe in the fields. I am running this from my localhost. I.e Nottingham is sometimes shortened to Nott'ham, but all that would be entered to the database is Nott

 

Any ideas why?

Link to comment
Share on other sites

How do you know nothing after the ' is inserted into the database? Have you looked directly in the database table using your favorite database management tool or does this symptom show up when you use php code to retrieve and display the value on a web page? If it is the latter, please post your code responsible for retrieving and displaying the value on a web page.

Link to comment
Share on other sites

The data doesn't have any apostrophes by the time it gets to mysql_real_escape_string(). If it did, they would either be inserted with the data, or if mysql_real_escape_string() didn't run (due to not being connected to the DB), the query would fail. The problem almost has to be in your html form, as PFMaBiSmAd has been trying to tell you. To test that theory, echo the variables before and after using mysql_real_escape_string on them and see what they contain.

Link to comment
Share on other sites

My money is on he has other sanitization going on, perhaps magic_quotes? but still it would generate an error for unknown data type.

But thing is as everyone has been saying, check the data along its path to the query.

 

to keep these debugging messages from distorting your pages, you can always put them in html comments

 

 echo '<--- SQL Query:'. $query .' --->'. PHP_EOL;

Link to comment
Share on other sites

OK, So I can put the apostrophe straight into a database using:

mysql_real_escape_string($AwayTeam)

kind of thing.

however I notice the problem is arising when I try to pass the word with an apostrophe in via a form.

If I disregard escaping it like so:

echo "<input type='hidden' name='AwayTeam' value= '".$user_array['COL 4']."' />";

Then everything before the apostrophe is posted, so if the word is Nott'ham, then Nott is posted.

If I put the escape in like so:

echo "<input type='hidden' name='AwayTeam' value= '".mysql_real_escape_string($user_array['COL 4'])."' />";

Then a slash is also posted, but not the apostrophe, and nothing after that, like so:

Nott\

Then I get an error because I am then trying to pass the word to a database, and it is trying to pass Nott\

 

....AND `COL 4` = 'Nott\')You have an error in your SQL syntax;....

Link to comment
Share on other sites

When populating the value attribute of a form element, you don't use mysql_real_escape_string(). As its name would imply, its purpose is to escape string type data to make it safe to use in a MySQL database query string. You should be using htmlentities with the ENT_QUOTES flag to populate the value= attribute. You can then use html_entity_decode before mysql_real_escape_string when storing the data, if you want the actual quotes stored in the database instead of their respective html entities.

 

The query is subsequently failing because you aren't using mysql_real_eacape_string prior to inserting the data. If you were, the backslash would be escaped again with another backslash and the query would insert the truncated text.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.