Hello,
i'd like to do a webpage that includes a searchable Database. In the end it should look somehow like this: http://www.animenfo.com/animetitle,1475,mlmoin,air_tv.html
Something where you have a page and many infos.
As for myself I'm a Webdesigner, but i dont know anything about SQL or so.. only HTML etc.
So whats the best way to make something like this? any links with help/introduction are appreciated.
Oh and btw, I already made a big Excel file with a lot of Data, if there is any way to import that it would be great.
An earlier version of http://www.oreilly.com/catalog/webdbapps2/ is what I used to get started with PHP and databases. It doesn't teach you to code well, but it gets you started on it -- you kinda have to learn or acquire the best practices as you go along. I recommend it.
Hint: Never, ever use the internal PHP SQL functions. Never.
Either use another language, or get one of the database access modules for PHP. You will find a thousand tutorials and guides to using the internal PHP mysql_* functions, but do not use them. They are horribly broken and you will write insecure code using them.
>or get one of the database access modules for PHP
Suggestions? I've always just used the mysql_ functions, because I've never heard any reason not to.
This is a glimpse into the reason why you should never use them: http://4-ch.net/code/kareha.pl/1120533289/9
Sadly, I'm not really a PHP developer so I can't recommend any of the alternatives. Something like http://pear.php.net/package/MDB2 is probably what you want.
>Sadly, I'm not really a PHP developer so I can't recommend any of the alternatives.
And yet you're ranting about how PHP's MySQL functions must be insecure? What do you think the various wrappers ultimately use?
The good books on PHP/MySQL (including the ones I linked to) will go over possible insecurities and give you hints on how to avoid them. Granted, they won't cover every possible exploit. But when it comes down to it, you only need to secure your database in proportion to the value of the data stored inside it. If you're not storing personal data, SSNs, credit card numbers, etc, then I wouldn't worry about trying to seal up every possible chink in the armor -- life's just too short. Get the obvious stuff out of the way (which, despite what PHP hataz say, is not only possible, it's quite easy) and get back to working on the fun stuff in your script).
> And yet you're ranting about how PHP's MySQL functions must be insecure? What do you think the various wrappers ultimately use?
Of course I am still ranting about it. You don't need to actually use an API every day to tell that it is horribly designed.
And yes, of course you can still write secure code with it. But the thing is, you won't. Because you won't think about every posible way to break string escaping, and so on. You yourself have posted broken code using this API on this very board, so you don't really have a high ground to take here.
The point is, you shouldn't have to think about this! You should use a well-designed API that protects you against common attacks by default! This is the case in most popular scripting languages other than PHP, and this is also the case with the third-party SQL libraries for PHP! That is how you "get the obvious stuff out of the way and get back to working on the fun stuff in your script". It's both easier to use, and much more secure.
The default PHP functions are broken by design. They just took the plain C API to mysql and ported it right into PHP. This is an API designed for experts who write code in C, and know what they're doing. It is most definitely not an API designed for inexperienced scripting-language coders, but it is shoved in front of them to shoot themselves in the foot with. And they shoot themselves in the foot with it. Over and over again. SQL injection attacks are far, far more common in PHP than in any other language.
Please stop being so stubbornly contrarian about this, and try to realize that this problem really does exist.
> You should use a well-designed API that protects you against common attacks by default!
DBI doesn't protect you against $dbh->prepare("$rawsql");
Also, it takes like 30 seconds to write a mysql_query wrapper that uses vsprintf and calls mysql_real_escape_string on its arguments.
> and get back to working on the fun stuff in your script
>>6
With a well designed API that jut does escaping for you, you never have to even think about the not-fun stuff, because the API writers have done the non-fun stuff for you. You just tell the API "Execute this statement with these parameters" and it does what it's supposed to, escapes everything and makes SQL injection virtually imposible (An Database interface that is beeing used by a few hundred persons is much less likeley to include errors than something you wrote yourself, right?).
I'ts not like I don't like PHP, it's nice to use for glueing together some small website, but for anything that is only a little bit more complex, i'd prefer a language that is easier to use and more powerful.
>>1
About the Excel thing: Excel should be able to export your data as a CSV file, which you should be able to import into your database with a little script or some programm.
> DBI doesn't protect you against $dbh->prepare("$rawsql");
No, but the docs will tell you again and again to NOT DO THAT. And neither will any of the example code you cut-and-paste do that. The only time this really is a problem is if your idea of how to build SQL statements has already been ruined by using PHP's API.
> Also, it takes like 30 seconds to write a mysql_query wrapper that uses vsprintf and calls mysql_real_escape_string on its arguments.
Nobody does. Ever. No tutorial tells you to. If you really do know what you're doing, you can easily do this, but in that case, you could use any API and write fairly secure code. The problem is the people who don't know any better.
>The problem is the people who don't know any better
... who, in turn, don't know to "just use something else", making the whole argument kinda pointless. Right?
Not if the subject is "PHP is a horrible language".
But you will note the discussion started with the suggestion to someone new to this to use something else.
I've been playing with PHP 5's SQLite capabilities with what may eventually become Thorn 2, and I gotta say I'm really liking it. Just the ability to instantly get all multiple-row results as a two-dimensional array (sqlite_array_query) instead of having to loop and build your own array makes for so much cleaner code. And the fact that it's much easier for both me and the end user to work with -- they don't have to set up databases and type in usernames and passwords -- is a huge incentive to really never use MySQL again for small-ish projects like this. (Instead of connecting to a server, SQLite just keeps its data in a single file which can be stored anywhere.)
Anyone else done any playing with SQLite? Your thoughts?
>>13
I've used SQLite for a couple of small-scale apps. It's nice, but I have to say I prefer using PDO over the sqlite_* functions. I work with several projects that use different database engines, and I can use the same PDO methods to access each of them. This also means that, should there ever be a need to port the application to a different database engine, I just need to change the database driver used when invoking PDO, and possibly tweak my SQL statements a little (since PDO isn't a database abstraction layer).
Are the sqlite_* functions as retarded as the mysql_* functions, or did they actually get it right?
I guess it depends on what you mean by "retarded." Do you mean to ask is it as easy to do SQL call exploits (I forget the proper term for them right now) as it is to do with the MySQL functions if the calls aren't properly escaped? I'm not sure, but I don't think so. However, there are other concerns with using SQLite databases. For example, the database is stored in a single file. If that file is stored in a publicly-accesable directory on the web server, then, well, it's publicly accessible -- anyone can download it and have a copy of your data.
Currently Thorn 2 is putting the database in such a directory and doesn't let you change it; this is going to be something I'm going to change soon. That being said, the database won't hold much info that isn't already publicly accessible just by browsing the boards anyway (the SHA1-crypted per-board moderator passwords are pretty much the only cause for concern I can think of off-hand).
More on security issues with regards to the location of the SQLite file here:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWebSecurity
One frustration that I'm running into is that the version of SQLite included with PHP seems to be some iteration of version 2, whereas the project is now up to version 3. That means that some SQL that should work according to the documentation simply doesn't when I try to use it, like ALTER TABLE and (most painfully) AUTOINCREMENT. Version 2 supports a form of auto-incrementation if I set the column's type as INTEGER PRIMARY KEY (INT PRIMARY KEY won't work, I discovered after much consternation, even though just INT for declaring an integer type column works just fine), but it's such so that if rows 1, 2 and 3 are created, then row 2 is deleted, the next row to be created will be numbered 2 instead of 4. (sigh)
http://www.sqlite.org/faq.html#q1
> but it's such so that if rows 1, 2 and 3 are created, then row 2 is deleted, the next row to be created will be numbered 2 instead of 4.
No, it's that if 3 is deleted, the next will be numbered 3. It's MAX(key)+1.
It seems a sane enough behaviour to me. If collisions with deleted keys is a problem, you should probably not be deleting them in the first place, but just marking them outdated or something, or else you have a constraint where certain keys are forbidden, but no list of which those are exists anywhere.
I stand corrected. If that is indeed the case, that's certainly less problematic than I was thinking.