1

I have this SQL:

INSERT INTO v3_peers 
                    (torrent, peer_id, ip, ipv6, port, smoke_ip, uploaded, downloaded, up_ofset, down_ofset, remaining, started, last_action, connectable, userid, agent) 
                    VALUES 
                    ('13471', '-UT2200-ĂZŤjśŻl‰şđ^', '69.64.147.243', '2001:0:5ef5:79fd:14e6:7214:2a5f:5811', '58262', '', '0', '0', '0', '0', '0', '1334945327', '1334945327', 'yes', '1', 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.162 Safari/535.19')

When it is executed in script it is saved to db, but peer_id is only "-UT2200-" and browser give me dialog to save file with name of sript and content whitch should be returned as php output in plain/text

When I insert this into db by phpmyadmin everything is ok.

peer_id have datatype varchar(255)

Any ideas what is wrong?

Koga
  • 135
  • 9
  • 1
    which encoding is your PHP-File? – Dion Apr 20 '12 at 18:19
  • are you properly escaping your values before trying to commit them to the database? – Jeremy Holovacs Apr 20 '12 at 18:20
  • PHP file is in utf-8. I was trying escaped it with mysql_real_escape_string - no change – Koga Apr 20 '12 at 18:22
  • Please stop writing new code with the ancient `mysql_*` functions. They are no longer maintained and community has begun the [deprecation process](http://news.php.net/php.internals/53799) . Instead you should learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysql). If you care to learn, [here](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) is a quite good PDO-related tutorial. – tereško Apr 20 '12 at 18:24
  • Whats the advantage of using PDO or MySQLi? I am always working with mysql-functions and its working perfect... – Dion Apr 20 '12 at 18:27

4 Answers4

1

You should pass the variables into the query in a safe manner. Rework your script and query so the variables are passed escaped. E.g:

$peer_id = mysql_real_escape_string('-UT2200-ĂZŤjśŻl‰şđ^');
$query = 'INSERT INTO `v3_peers` (`peer_id`) VALUES ("' . $peer_id . '")';
$result = mysql_query($query);

This way you make sure that the variable does not impact the query structure.

Additionally, and as @tereško mentioned, try PDO. Your query looks like it will be frequently utilized, so PDO and its prepared statements may come in handy. I explained that recently to someone: PDO prepared statements to store html content.

Community
  • 1
  • 1
Taz
  • 3,718
  • 2
  • 37
  • 59
  • I made a mistake in the query - forgot to quote the string. – Taz Apr 20 '12 at 18:44
  • @Koga I just tried it with my script `utf-8` decoded and table `utf8 - utf8_unicode_ci` collation. Worked perfectly. You mentioned that your script is `utf-8` - does this apply to bot script encoding and page headers? – Taz Apr 20 '12 at 18:50
  • Look at my comment at 3rd answer. There is another one character whitch is not copied. It have HEX representation as "01". – Koga Apr 20 '12 at 18:52
  • 1
    @Koga No unless you pass it not quoted. Look at this fiddle: http://sqlfiddle.com/#!2/83906/1. It works. You either have something wrong with encoding or with the code. Could you post the part of your php code that is dealing with the query? – Taz Apr 20 '12 at 19:04
  • There is not doing with peer_id variable, it is same as $_GET[peer_id]. I am sure that there is encoding problem, but i dont know whitch one. – Koga Apr 20 '12 at 19:46
  • @Koga OK. Here's what you have to do to make sure your app supports utf-8: http://stackoverflow.com/questions/279170/utf-8-all-the-way-through#279279 – Taz Apr 20 '12 at 19:54
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10336/discussion-between-koga-and-struna) – Koga Apr 20 '12 at 20:37
0

I beleave that the MySQL is not allowing the special characters

kyle1046
  • 383
  • 4
  • 18
  • Partially true. The problem is the encoding of the characters. If used correctly, MySQL would allow it. – Simon Forsberg Apr 20 '12 at 18:21
  • I found one character whitch was not copied to my first post. It is between characters "ş" and "đ", in saved log file it is showed as square. But in hex format it is as dot with hex representation "01". Can by this that error? – Koga Apr 20 '12 at 18:30
  • 1
    @koga if it is a box it means that you are missing an language package if it cant display a character it displays a box – kyle1046 Apr 20 '12 at 18:52
0

Maybe try it with utf8_decode()

Dion
  • 3,145
  • 20
  • 37
-1

Ok, I found the error. When i am comparing datas inserted in db and actual data from $peer_id=$_GET[peer_id] variable in SQL like:

WHERE peer_id LIKE '".$peer_id."'

then I got error because peer_id in db is not like peer_id, but when i use

WHERE peer_id = '".$peer_id."'

it works perfectly. Encoding of column peer_id must be set to utf8-unicode-ci NOT utf8-general-ci!!

So, there was not error in encoding (missing characters in phpmyadmin as I wrote in question are not realy missing but hidden because of encoding) but error is in other scrip.

Thanks all for your time. I hope this will help someone in future.

Koga
  • 135
  • 9