Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.
SQL has some drawbacks, the worst one might be the SQL injection chance. There are many ways to avoid them but few of them are really working, one of them is using bind values by replacing all values by ? and passing the real values at execution time.
You might think that this would happen when using bind values with DBI:
| Perl statement | Send to SQL server |
| $sth = $dbh->prepare('SELECT ?'); | prepare SELECT ? |
| $sth->execute(1); | execute values 1 |
| Perl statement | Send to SQL server |
| $sth = $dbh->prepare('SELECT ?'); | (nothing) |
| $sth->execute(1); | execute SELECT '1' |
strace perl -MDBI -le 'my $dbh = DBI->connect("DBI:mysql:database=test;host=db_server","username","password");print "got dbh: $dbh";print "sth: ".($sth = $dbh->prepare("SELECT ?"));print "execute: ".$sth->execute(1);'(Remove the newlines when testing, they're for readability only.)strace says:
connect(3, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("127.0.0.1")}, 16) = 0[...]write(1, "got dbh: DBI::db=HASH(0x1624d10)"..., 33) = 33write(1, "sth: DBI::st=HASH(0x1624968)\n", 29) = 29write(3, "\v\0\0\0\3SELECT '1'", 15) = 15read(3, "\1\0\0\1\1\27\0\0\2\3def\0\0\0\0011\0\f\10\0\1\0\0\0\375\1\0\37\0\0"..., 16384) = 56write(1, "execute: 1\n", 11) = 11DBD::mysql replaces all placeholders before sending everything to the server. I don't know why, but I'm not really mad with the module.Using placeholders with the database might be faster, expecially for complex queries, but I don't know if the mySQL binary protocol actually supports them (it didn't even support prepared statements for a long time and doesn't support them very well today). I don't know any possibility of injecting malicous SQL code when bind values are being used, so using them is still a huge security improvement even if it doesn't speed up things.
5 Kommentare. Schreib was dazu-
![]()
Martin J. Evans
2.07.2012 19:49
Antworten
-
![]()
Martin J. Evans
2.07.2012 19:51
Antworten
-
![]()
Sebastian
3.07.2012 12:52
Antworten
-
![]()
Martin J. Evans
4.07.2012 12:05
Antworten
-
![]()
Sebastian
5.07.2012 22:14
Antworten
Did you try setting mysql_server_prepare=1? Because I think this is what you want.
Which you'll find documented at https://metacpan.org/module/DBD::mysql#Prepared-statement-support-server-side-prepare-
Yes, I tried, but it's really slow compared to prepare and prepare_cached and it fails some tests coming with DBD::mysql.
Initially I thought about suggesting you rt'd your issues with mysql_server_prepare but then I saw the RT queue for DBD::mysql. It looks as though the maintainer might need some help.
BTW, even though I checked the box below saying email with further comments I did not seem to get that email - could be a problem my end.
I'm not sure but I assume that the mySQL prepared statement handling is bad, not DBD::mysql being the problem. It looks like mySQL isn't pre-parsing and preparing the statements internally but just store and parse them on every execute (using Perl and strace).