Reply to: Why is the DB on this forum so dodgy?

[Dundeegeorge]

All very interesting indeed

but where's the abuse?
Or at least a picture of some tits? (cue the hysterical pictures of parus major)

[TheMonkey]

Yeah the mysqli extension does that using recent mysql client libs. Only problem is that 90% of crappy php web hosts don't use it.

mysql_escape_string IS safe. I've read the source code!

[Cowboy Bob]

Do you mean that strings should never be passed through, and the query should be abstracted from the actual SQL call - if so, I entirely agree.

To illustrate...

If an app needs to return a record for a an Engineering Part, let's say, then the app should provide a GetPartRecordByNo(string partNo) method.

Rather than GetWhateverIWant("SELECT * FROM TABLE_PARTS");

It's only right!

Nope. It should be something like this - in whatever syntax you prefer:-

PreparedStatement stmt = connection.prepareStatement("select * from table_parts where partNo = ?");
stmt.setString(1, partNo);
stmt.execute();

This leaves the string escaping to the DB itself, which is a much better way of doing things as it is more likely to know what's best.

[Cowboy Bob]

I use PHP quite regularly. My code DOES escape strings, but safely:

	/**
	 * Login and set cookie etc.
	 */
	function security_login($email_address, $password, $remember_me) {
		$sql = sprintf("SELECT user_id FROM user WHERE email_address = '%s' AND password = MD5('%s');", 
			mysql_escape_string($email_address), 
			mysql_escape_string($password));
		print_r($this->database->querySingleRow($sql));
	}

Note - that is seriously unfinished part of the app's API.

Ding, you're a winner!!! You should never escape strings to pass into DB queries - it's inherantly unsafe with regards to SQL injection. I know you're going to say that the mysql_escape_string escapes the string to make it safe, but how do you know it catches ALL eventualities? It's such a weak point, don't trust it. There are better ways. You should always use parameterized queries. I understand PHP has something called PearDB that can do this. A PreparedStatement is what it's called in the Java world.

[TheMonkey]

I use PHP quite regularly. My code DOES escape strings, but safely:

	/**
	 * Login and set cookie etc.
	 */
	function security_login($email_address, $password, $remember_me) {
		$sql = sprintf("SELECT user_id FROM user WHERE email_address = '%s' AND password = MD5('%s');", 
			mysql_escape_string($email_address), 
			mysql_escape_string($password));
		print_r($this->database->querySingleRow($sql));
	}

Note - that is seriously unfinished part of the app's API.

[bogeyman]

I've yet to meet any other sort. Just look here - http://thedailywtf.com/forums/thread/87226.aspx - where they're all banging on about using escaped strings to pass into SQL statements. And these guys nearly all seem to think they're experts.

(If you have to question why escaping strings is bad, put yourself into the aforementioned category).

Do you mean that strings should never be passed through, and the query should be abstracted from the actual SQL call - if so, I entirely agree.

To illustrate...

If an app needs to return a record for a an Engineering Part, let's say, then the app should provide a GetPartRecordByNo(string partNo) method.

Rather than GetWhateverIWant("SELECT * FROM TABLE_PARTS");

It's only right!

[AtW]

I view strings as array of bytes, do I need to escape numbers too?

[Cowboy Bob]

crappy PHP developers

I've yet to meet any other sort. Just look here - http://thedailywtf.com/forums/thread/87226.aspx - where they're all banging on about using escaped strings to pass into SQL statements. And these guys nearly all seem to think they're experts.

(If you have to question why escaping strings is bad, put yourself into the aforementioned category).

[AtW]

Yes, crappy developer do not write set matching in assembly, amateurs.

[TheMonkey]

Let's help people understand MySQL a little better and jump to it's defense a little...

- It has referential integrity.
- It has programmable constraints
- It has stored procedures
- It has views.
- It has replication.
- It has a tiered security architecture.
- It has full text indexing that works REALLY well.
- It does ANSI SQL.
- It is extremely fast.
- It runs on more platforms than any other RDBMS.
- It has decent development tools.
- It is cheap as in £0
- It is easy to install and use.
- It has a clustering facility.
- It has good documentation.
- It has .Net providers, JDBC providers, ODBC providers and modules for almost every scripting language on the planet.

99% of MySQL's problems are the crappy PHP developers who insist on backwards compatibility with version 4.1 and below.

[hyperD]

Apologies if the forum outages caused you to do any work, we know this is inexcusable and we will be working hard to make sure you are not distracted by work again in the future.

LOL! Yes, quite impressed with the last hour - you must do more of that outage please!!

One small thing, Mr Ferret: the emoticons seem to render every time when you get to the reply/add new post page.

Any chance of caching them because until they've rendered, the Toolbar buttons seem to create a JavaScript error if you click on them prematurely?

[bogeyman]

Only a finer point??? I'd argue that this is the cause of many a forum crash due to keys getting out of step with different PHP scripts updating the same tables. Just a hunch...

EDIT: Notice that the breadcrumbs on the top of the forum throw errors right now? I'd lay money on that being some sort of PK clash.

True RI is important but we had RDBMSs for many years before RI was the norm. You just had to do the referentiality checking in code. Lazy BB coding I suspect, rather than MySQL's fault.

I do, however, agree that it would be nice if MySQL had a coherent RI implemention.

[Cowboy Bob]

(maybe because it skips on some of the finer points, like referential integrity checking)

Only a finer point??? I'd argue that this is the cause of many a forum crash due to keys getting out of step with different PHP scripts updating the same tables. Just a hunch...

EDIT: Notice that the breadcrumbs on the top of the forum throw errors right now? I'd lay money on that being some sort of PK clash.

[bogeyman]

Obviously not seeing the number of outages on this site. Not exactly high throughput is it? A few posts a minute.

Well it depends on the server resources available, doesn't it?

MySQL is generally regarded as fairly high throughput (maybe because it skips on some of the finer points, like referential integrity checking).

You can't blame the DB for being on a crap underpowered server.

[AtW]

Its most likely shared server