SQL insert into with mysql_real_escape_string

[a.mlw.walker]

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?

[PFMaBiSmAd]

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.

[a.mlw.walker]

I can see it in phpmyadmin. I am trying to add Nott'ham, but only Nott appears in the database table when i look at it from PhpMyAdmin. Sorry I should have stated that

[PFMaBiSmAd]

Echo your $sql variable after you form the query in it. You will probably find that nothing is present after the ' from your form, most likely because your HTML of your form is missing some quotes around a value= '.....' attribute.

[a.mlw.walker]

Ah Not so easy now. I only spotted it because an example came up. I now need another example to appear.

It was hapening here:

.mysql_real_escape_string($GameKey)

yet the escape was not solving the problem. I thought it may have been a known thing...

[a.mlw.walker]

I mean, as long as the entry doesnt have an apostrope, then the sql statement is fine. Its not a typo because it works otherwise...

[PFMaBiSmAd]

And where does the data come from that is being put into the query statement?

[Pikachu2000]

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.

[laffin]

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;

[a.mlw.walker]

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;....

[Pikachu2000]

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.

[a.mlw.walker]

You guys are too good!