Simple enough, i need to post a string(32), string(256),string(32) and a integer into a MySQL database, I've tried to filter out characters as ',; and putting everything to lowercase (Don't know if thats any use in the Sql query) but all have failed, do you guys have a premade php function to filter a string?
[QUOTE=CombineGuru;27212328][url]http://php.net/manual/en/function.mysql-real-escape-string.php[/url]
[php]ereg('[^A-Za-z0-9.-]', $rq)[/php]
[editline]5th January 2011[/editline]
Oh wait, ereg is deprecated. :smithicide:[/QUOTE]
I was using that at first, but i tried to add ? and ! (Its a bug report form) and messed it up.
[QUOTE=Goz3rr;27231887]I was using that at first, but i tried to add ? and ! (Its a bug report form) and messed it up.[/QUOTE]
just escape it
Don't escape it.
Learn to use proper parameterized queries.
I don't know what language you're writing in (you didn't mention it), so look up tutorials on bound statements, or prepared statements and read through it.
It really is quite straight forward and saves any hassle worrying about escaping strings, SQL injection, etc.
[QUOTE=DEADBEEF;27234426]Don't escape it.
Learn to use proper parameterized queries.[/QUOTE]
We all know parameterized queries are better, but do you really think we can expect OP to be able to figure out parameterized queries based on what he's posted so far?
Baby steps...
[QUOTE=DEADBEEF;27234426]Don't escape it.
Learn to use proper parameterized queries.
I don't know what language you're writing in (you didn't mention it), so look up tutorials on bound statements, or prepared statements and read through it.
It really is quite straight forward and saves any hassle worrying about escaping strings, SQL injection, etc.[/QUOTE]
Im writing a string for the title, a string for the actual report, a 1,2 or 3 as int for urgency and a string for the category.
Also it's in PHP
[QUOTE=Combino;27234500]We all know parameterized queries are better, but do you really think we can expect OP to be able to figure out parameterized queries based on what he's posted so far?
Baby steps...[/QUOTE]
What? prepared statements aren't in any way complex.
He hasn't really posted anything so far but assuming php, it's pretty much as simple as....
[php]$dbo = new mysqli( 'host', 'user', 'password', 'database' );
$statement = $dbo->prepare( "INSERT INTO users VALUES ( ?, ?, ? )" );
$statement->bind_param( 'sss', $name, $email, $password );
$statement->execute();
$statement->close();
[/php]
That's it. No fucking about escaping strings, no fucking about worrying about illegal characters, no worrying about unicode screwing it up, just prepare the statement and insert the data.
Personally I'd use PDO, but mysqli is easy enough. If you are capable of using regular sql statements you can (and should) use prepared statements.
Here's the non-OO way if that floats your boat...
[php]
$dbo = mysqli_connect( 'host', 'user', 'password', 'database' );
$statement = mysqli_prepare( $dbo, "INSERT INTO users VALUES ( ?, ?, ? )" );
mysqli_stmt_bind_param( $statement , 'sss', $name, $email, $password );
mysqli_stmt_execute( $statement );
mysqli_stmt_close( $statement );
[/php]
SELECT queries are just as straight forward.
Obviously, you should really check the db connection was created before executing anything, but that would be explained in a tutorial.
[url=http://devzone.zend.com/article/686]Here is such a tutorial.[/url]
[editline]6th January 2011[/editline]
Goz3rr, check the tutorial above, this is how you should be doing it.
Forget escaping, no-one in their right mind should be teaching you that, not since the advent of the MySQLi/PDO extensions.
@DEADBEEF:
He couldn't even figure out the mysql_real_escape_string() function. That's a whole different level of complex.
[QUOTE=Combino;27236933]@DEADBEEF:
He couldn't even figure out the mysql_real_escape_string() function. That's a whole different level of complex.[/QUOTE]
You have to start somewhere, and you're far better off being taught the right way to do something from the outset rather than learn the wrong way and have to un-learn it later.
For reference, here's the escaping way to do it...
[php]mysql_connect("host", "user", "password");
mysql_select_db("database");
$query = sprintf("INSERT INTO users VALUES ( '%s','%s','%s')",
mysql_real_escape_string( $user ),
mysql_real_escape_string( $email ),
mysql_real_escape_string( $password )
);
mysql_query( $query );
mysql_close();
[/php]
It's hardly any more straightforward than the proper way, and much less secure.
The only difference is that rather than adding the escaped strings via sprintf and sending them as part of the query we're binding them and sending them to the database separate from the rest of the query (and utterly negating any possible SQL injection attack vectors).
[QUOTE=DEADBEEF;27237429]You have to start somewhere, and you're far better off being taught the right way to do something from the outset rather than learn the wrong way and have to un-learn it later.
For reference, here's the escaping way to do it...
[php]mysql_connect("host", "user", "password");
mysql_select_db("database");
$query = sprintf("INSERT INTO users VALUES ( '%s','%s','%s')",
mysql_real_escape_string( $user ),
mysql_real_escape_string( $email ),
mysql_real_escape_string( $password )
);
mysql_query( $query );
mysql_close();
[/php]
It's hardly any more straightforward than the proper way, and much less secure.
The only difference is that rather than adding the escaped strings via sprintf and sending them as part of the query we're binding them and sending them to the database separate from the rest of the query (and utterly negating any possible SQL injection attack vectors).[/QUOTE]
I just do it:
[php]
$user = mysql_real_escape_string(htmlspecialchars($user));
$email = mysql_real_escape_string(htmlspecialchars($email));
$password = mysql_real_escape_string(htmlspecialchars($password));
$query = "INSERT INTO users VALUES ('$user', '$email', '$password')";
[/php]
[QUOTE=supersnail11;27242415]I just do it:
[php]
$user = mysql_real_escape_string(htmlspecialchars($user));
$email = mysql_real_escape_string(htmlspecialchars($email));
$password = mysql_real_escape_string(htmlspecialchars($password));
$query = "INSERT INTO users VALUES ('$user', '$email', '$password')";
[/php][/QUOTE]
Why do people escape HTML before inserting it into the database?
It's far better to leave everything as-is and escape only on output.
Yes, that does the same thing, although you'd normally do htmlspecialchars when displaying the data, not before putting it in the DB.
You wouldn't use that in production code though right? you'd use proper parameterized queries, yeah?
It absolutely beggars belief belief that people still do character escaping still, when it's been proven time and time again to be a high risk SQL injection attack vector. Why bother risking it?
Just learn to do it properly FFS.
[QUOTE=DEADBEEF;27245656]
It absolutely beggars belief belief that people still do character escaping still, when it's been proven time and time again to be a high risk SQL injection attack vector. Why bother risking it?
Just learn to do it properly FFS.[/QUOTE]
It's an argument we see every day here in web dev :/
To be fair, I'm on the side of escaping, but not like that; I've written my own self-protecting db class, which uses basic mysql_query because I prefer the format of writing queries, but also provides perfect SQL injection protection in itself. But I admit, in 99% of cases, parametrized queries are the way.
[QUOTE=Fizzadar;27247106]It's an argument we see every day here in web dev :/
To be fair, I'm on the side of escaping, but not like that; I've written my own self-protecting db class, which uses basic mysql_query because I prefer the format of writing queries,[/QUOTE]
Parameterized queries are just the same, and I'd argue that they actually look neater.
Compare this:
[php]
mysql_query("SELECT * FROM files WHERE folder = $folderid ORDER BY time DESC LIMIT " . ($page - 1) * $numperpage . ", $numperpage");
[/php]
To this:
[img]http://ahb.me/1o3V[/img]
If you can write a wrapper for mysqli that lets you pass the parameters in as extra arguments to a method, there's practically no difference in how the query looks.
Not to mention that using parameterized queries allows the database to compile queries and cache them for improved performance. It can't do this if you just escape data because every single query is different.
MySQL provides a mechanism for providing parameters to queries, so why not use it??
[QUOTE=Combino;27247358]
MySQL provides a mechanism for providing parameters to queries, so why not use it??[/QUOTE]
MySQL caches statements either way, and you can force it to within the statement itself. mysql_query is also faster; but it's marginal, it would be over-optimizing of anyone to change for that reason alone.
I can see the point about it looking better, but as the other thread (echo '.' stuff), I prefer to keep it all inline, one string-style, for that kind of stuff. Quick bits of PHP within html and quick bits of PHP within SQL as it were.
As I said, it's personal preference more than anything else. The good thing about having it all in a class is that I can easily switch if/when I feel like it, but I'm quite happy with the basic functions currently.
[QUOTE=Fizzadar;27247683]MySQL caches statements either way, and you can force it to within the statement itself.[/QUOTE]
MySQL can't cache a query if you have variable data inside the query itself.
If you use the parameter facility MySQL provides, you can use the same query with different parameters and MySQL will compile and cache the query itself.
[QUOTE=Combino;27247780]MySQL can't cache a query if you have variable data inside the query itself.
If you use the parameter facility MySQL provides, you can use the same query with different parameters and MySQL will compile and cache the query itself.[/QUOTE]
True, I'd say that is one clear advantage, unless you're doing hundreds of identical queries, but that's pretty unlikely.
It could also be done using mysql_query by writing prepared statements myself, then executing them, but the performance gain isn't huge.
It still comes down to personal preference, I'm not going to be needing such optimization on my sites.
[QUOTE=Fizzadar;27248055]True, I'd say that is one clear advantage, unless you're doing hundreds of identical queries, but that's pretty unlikely.[/QUOTE]
With escaping, you don't get any advantage from the compiler and cache. These are all different queries:
[list]
[*]SELECT * FROM users WHERE id = 1
[*]SELECT * FROM users WHERE id = 2
[*]SELECT * FROM users WHERE id = 3
[*]SELECT * FROM users WHERE id = 4
[*]SELECT * FROM users WHERE id = 5
[*]SELECT * FROM users WHERE id = 6
[*]SELECT * FROM users WHERE id = 7
[*]SELECT * FROM users WHERE id = 8
[*]SELECT * FROM users WHERE id = 9
[/list]
With parameterized queries, you have a single query:
[list][*]SELECT * FROM users WHERE id = ?[/list]
Which is reused for every single value of ?. This way you [b]will[/b] have hundreds of identical queries, and MySQL will compile that query, and keep it cached so that subsequent queries will skip the parsing and lexing phase.
It's not about optimization (not only), it's mainly about security.
Escaping is open to certain types of SQL injection if your database character set isn't set properly, and you aren't extremely careful what character sets you'll accept as user input.
Far easier to just use prepared statements and you don't have to worry about it at all.
The fact that it saves bandwidth when doing transactions, and CPU cycles on your DB server only really comes into play once you're doing millions of transactions/queries per hour. Most people don't need to worry about it.
[QUOTE=DEADBEEF;27236883]Goz3rr, check the tutorial above, this is how you should be doing it.
Forget escaping, no-one in their right mind should be teaching you that, not since the advent of the MySQLi/PDO extensions.[/QUOTE]
Thank you, this looks like a nice way to do it.
[QUOTE=Combino;27236933]@DEADBEEF:
He couldn't even figure out the mysql_real_escape_string() function. That's a whole different level of complex.[/QUOTE]
I know it existed, but i heard there were better ways.
[editline]7th January 2011[/editline]
[QUOTE=DEADBEEF;27249956]It's not about optimization (not only), it's mainly about security.
[/QUOTE]
I must agree with deadbeef here, security is a big issue for us, because the database is also being used to store player money and other stuff from our SA-MP Gamemode.
Would i also have to enable the mysqli extension in my php.ini?
Sorry, you need to Log In to post a reply to this thread.
