Re: [PHP] Re: PHP MySQL insert
On 8/19/05, Richard Lynch [EMAIL PROTECTED] wrote: On Fri, August 19, 2005 12:56 pm, areguera wrote: could you suggest something about Latin characters and portability?. As I understand it, or not, more likely, you want to configure your MySQL server to use UTF-8, and your MySQL client to use UTF-8 and pretty much everything to use UTF-8, and then you can convert your data to UTF-8 and its gonna store it in a way that you'll be able to convert back to Latin-1 or whatever you like. At least, that's what Mark Matthews of MySQL A/B said in his talk about this at our more recent Chicago MySQL User Group meeting. You may want to take this question to the i18n PHP list, where people who have actually done it hang out. :-) thanks Richard, I'll make a walk around i18n php list...:) ... it seems that utf-8 is the solution for internationalization, but I ask my self what would happen with prior versions of mysql without utf-8 support? and how to design an application to both run as utf-8 or iso-8859-1 in the require situation. can it be? -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: PHP MySQL insert
Please always reply to the list so that others can benefit from the exchange. As it happens, I'm not exactly very knowledgeable about character sets, so someone on the list may be able to offer more help with regard to the problem you're experiencing. -Ben areguera wrote: On 8/19/05, Ben Ramsey [EMAIL PROTECTED] wrote: Alain Reguera Delgado wrote: you could try: 1. get all form variables into an array fine 2. validate values Good, but do this step as you put the values into a separate array, don't put all the values into the array first and then validate them later... make sure the input received is input expected and then save only the input to the array that passes the validation/filtering tests yes .. that's much better .. :) 3. convert all values into entities using htmlentities() Why do you want to do this before saving to the database? Ben, I got some troubles when moving database from one server to another, all Latin characters disappear, and the info turns a mess. Thought for a moment a server's language configuration setting. I was wondering by days to take this way, I thought if someone else wants the application and occurs the same because his configuration is not like mine. Then that solution came to me. Felt no matter what version or configuration of mysql or other db is used or what latin char is inserted, the data always be there for the web, in the language it speaks. This step has absolutely no bearing on preparing the statement for insertion into a database. It won't protect against SQL injection. Also, you will never be able to do anything with this data other than use it for HTML output (unless you try to reverse the entities, which seems like an awful lot of work to me). yes, I don't like either...its not flexible. It's best to save the raw data as entered and escape it (with htmlentities() or something else) ONLY on output. that was the first way I used to go... but after that problem, I am not sure As I mentioned in my last post to this thread, the best way to escape a string for insertion into a database (and protect against SQL injection) is to use the escape function for the particular database -- mysql_real_escape_string() in this case. You should never use htmlentities() to escape data before saving it to a database. Do that only after you've pulled data from the database and are outputting it somewhere (like on a Web page). 4. build sql query (do some tests 'til get it right) 5. execute the built query (with proper db function) by now, commas aren't a problem, they are limited between sql query's quotes. If some quotes are inserted as value they are previously converted to its entities and do not break the sql query. This is why you use mysql_real_escape_string(), etc. -- not htmlentities(). as previously said in this thread, the problem is on quoting and maybe on converting the values to entities, to prevent some quote break the sql structure. You don't need to convert the values to HTML entities when saving to a database. That's not going to prevent this problem. could you suggest something about Latin characters and portability?. Thanks for your time Ben. I am new in the list and in php too. Thanks for your answers. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: PHP MySQL insert
sorry...here is the message On 8/19/05, areguera [EMAIL PROTECTED] wrote: On 8/19/05, Ben Ramsey [EMAIL PROTECTED] wrote: Alain Reguera Delgado wrote: you could try: 1. get all form variables into an array fine 2. validate values Good, but do this step as you put the values into a separate array, don't put all the values into the array first and then validate them later... make sure the input received is input expected and then save only the input to the array that passes the validation/filtering tests yes .. that's much better .. :) 3. convert all values into entities using htmlentities() Why do you want to do this before saving to the database? Ben, I got some troubles when moving database from one server to another, all Latin characters disappear, and the info turns a mess. Thought for a moment a server's language configuration setting. I was wondering by days to take this way, I thought if someone else wants the application and occurs the same because his configuration is not like mine. Then that solution came to me. Felt no matter what version or configuration of mysql or other db is used or what latin char is inserted, the data always be there for the web, in the language it speaks. This step has absolutely no bearing on preparing the statement for insertion into a database. It won't protect against SQL injection. Also, you will never be able to do anything with this data other than use it for HTML output (unless you try to reverse the entities, which seems like an awful lot of work to me). yes, I don't like either...its not flexible. It's best to save the raw data as entered and escape it (with htmlentities() or something else) ONLY on output. that was the first way I used to go... but after that problem, I am not sure As I mentioned in my last post to this thread, the best way to escape a string for insertion into a database (and protect against SQL injection) is to use the escape function for the particular database -- mysql_real_escape_string() in this case. You should never use htmlentities() to escape data before saving it to a database. Do that only after you've pulled data from the database and are outputting it somewhere (like on a Web page). 4. build sql query (do some tests 'til get it right) 5. execute the built query (with proper db function) by now, commas aren't a problem, they are limited between sql query's quotes. If some quotes are inserted as value they are previously converted to its entities and do not break the sql query. This is why you use mysql_real_escape_string(), etc. -- not htmlentities(). as previously said in this thread, the problem is on quoting and maybe on converting the values to entities, to prevent some quote break the sql structure. You don't need to convert the values to HTML entities when saving to a database. That's not going to prevent this problem. could you suggest something about Latin characters and portability?. -- Ben Ramsey http://benramsey.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: PHP MySQL insert
On Fri, August 19, 2005 12:56 pm, areguera wrote: could you suggest something about Latin characters and portability?. As I understand it, or not, more likely, you want to configure your MySQL server to use UTF-8, and your MySQL client to use UTF-8 and pretty much everything to use UTF-8, and then you can convert your data to UTF-8 and its gonna store it in a way that you'll be able to convert back to Latin-1 or whatever you like. At least, that's what Mark Matthews of MySQL A/B said in his talk about this at our more recent Chicago MySQL User Group meeting. You may want to take this question to the i18n PHP list, where people who have actually done it hang out. :-) -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP MySQL insert
Commas are no problem within strings. You might have an apostrophe, which SQL assumes is the end of the string literal. That was answered by Chris already, I just wanted to clarify the problem. You don't need to insert NULL in indx. If indx allows NULL and has no other default value nor is it autoincrement by not mentioning in the list of columns nor specifying a value, it will get a NULL. Satyam Jon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Please help with an insert problem. Sometimes $data1 could have a comma and that messes up the insert. how do I get around that? $query = insert into testtable6 (indx, col1, col2) values (NULL, '$data1', '$data2'); mysql_db_query(testdb, $query); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP MySQL insert
Jon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Please help with an insert problem. Sometimes $data1 could have a comma and that messes up the insert. how do I get around that? $query = insert into testtable6 (indx, col1, col2) values (NULL, '$data1', '$data2'); mysql_db_query(testdb, $query); You are looking for the addslashes function. It prepares data for database querys: $query = insert into testtable6 (indx, col1, col2); $query .= values (NULL, ' . addslashed($data1) . '; $query .= ,' . addslashed($data2) . '; mysql_db_query(testdb, $query); Also, you will need to use the removeslashes function when you get data from a query. DanB -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PHP MySQL insert
Dan Baker wrote: You are looking for the addslashes function. It prepares data for database querys: Better yet, don't use addslashes(). Use the escaping function that is specific to the database you're using. In this case, it's mysql_real_escape_string(). This is much better than using addslashes() because it takes into account the current character set of the database connection. http://www.php.net/mysql_real_escape_string Also, you will need to use the removeslashes function when you get data from a query. If you properly store data to a database, you should never have to use the stripslashes() function. Using stripslashes() will remove slashes that were intended to be in the output. Hint: turn off magic_quotes_gpc. -- Ben Ramsey http://benramsey.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: PHP MySQL insert
you could try: 1. get all form variables into an array 2. validate values 3. convert all values into entities using htmlentities() 4. build sql query (do some tests 'til get it right) 5. execute the built query (with proper db function) by now, commas aren't a problem, they are limited between sql query's quotes. If some quotes are inserted as value they are previously converted to its entities and do not break the sql query. as previously said in this thread, the problem is on quoting and maybe on converting the values to entities, to prevent some quote break the sql structure. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: PHP MySQL insert
Alain Reguera Delgado wrote: you could try: 1. get all form variables into an array fine 2. validate values Good, but do this step as you put the values into a separate array, don't put all the values into the array first and then validate them later... make sure the input received is input expected and then save only the input to the array that passes the validation/filtering tests 3. convert all values into entities using htmlentities() Why do you want to do this before saving to the database? This step has absolutely no bearing on preparing the statement for insertion into a database. It won't protect against SQL injection. Also, you will never be able to do anything with this data other than use it for HTML output (unless you try to reverse the entities, which seems like an awful lot of work to me). It's best to save the raw data as entered and escape it (with htmlentities() or something else) ONLY on output. As I mentioned in my last post to this thread, the best way to escape a string for insertion into a database (and protect against SQL injection) is to use the escape function for the particular database -- mysql_real_escape_string() in this case. You should never use htmlentities() to escape data before saving it to a database. Do that only after you've pulled data from the database and are outputting it somewhere (like on a Web page). 4. build sql query (do some tests 'til get it right) 5. execute the built query (with proper db function) by now, commas aren't a problem, they are limited between sql query's quotes. If some quotes are inserted as value they are previously converted to its entities and do not break the sql query. This is why you use mysql_real_escape_string(), etc. -- not htmlentities(). as previously said in this thread, the problem is on quoting and maybe on converting the values to entities, to prevent some quote break the sql structure. You don't need to convert the values to HTML entities when saving to a database. That's not going to prevent this problem. -- Ben Ramsey http://benramsey.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: PHP MySQL insert
Ben Ramsey wrote: You don't need to convert the values to HTML entities when saving to a database. That's not going to prevent this problem. Furthermore, you don't need to use htmlentities() if you specify your character set properly and all the characters you are outputting are in your character set. For example, I use UTF-8 for all output, and all the characters I ever use are (of course) in the UTF-8 character set. Therefore I only need to use htmlspecialchars() to turn characters that have special meaning in HTML (, ', , , , etc.) into entities. Jasper -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
