a.mlw.walker Posted January 21, 2012 Share Posted January 21, 2012 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? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted January 21, 2012 Share Posted January 21, 2012 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. Quote Link to comment Share on other sites More sharing options...
a.mlw.walker Posted January 21, 2012 Author Share Posted January 21, 2012 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 Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted January 22, 2012 Share Posted January 22, 2012 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. Quote Link to comment Share on other sites More sharing options...
a.mlw.walker Posted January 22, 2012 Author Share Posted January 22, 2012 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... Quote Link to comment Share on other sites More sharing options...
a.mlw.walker Posted January 22, 2012 Author Share Posted January 22, 2012 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... Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted January 22, 2012 Share Posted January 22, 2012 And where does the data come from that is being put into the query statement? Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 22, 2012 Share Posted January 22, 2012 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. Quote Link to comment Share on other sites More sharing options...
laffin Posted January 22, 2012 Share Posted January 22, 2012 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; Quote Link to comment Share on other sites More sharing options...
a.mlw.walker Posted January 22, 2012 Author Share Posted January 22, 2012 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;.... Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 22, 2012 Share Posted January 22, 2012 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. Quote Link to comment Share on other sites More sharing options...
a.mlw.walker Posted January 22, 2012 Author Share Posted January 22, 2012 You guys are too good! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.