How to find out when an escape is needed for MySQL

Question

I am working on building a site using CodeIgniter. This is the first site that I created myself that interacts with the database. I am using MySQL for this project. How can I determine if data should be escaped before saving the database?

Answer 1

If you use a database class with query bindings , you do not need to make any manual screens:

The second advantage of using bindings is that the values ​​automatically slipped away, creating more secure queries. You do not need to manually remember the output data; the engine does this automatically for you.

Answer 2

I would advise you to train yourself to use prepared statements. Especially since you are new to working with databases. The sooner you start using them, the easier it will become second nature.

For example, I did not know about prepared statements when I started with databases. And I experienced my own isolation when I contacted them. Because I'm already used to another way of doing something already. Now it may not be a trade in a character, but it does not hurt to start as soon as possible with him in any case.

Prepared statements allow you to use placeholders in your queries. Then these placeholders can be replaced with actual values, linking them to placeholders. This binding process automatically escapes values.

Here's a (simple) PDO example:

$db = new PDO( /* some database parameters */ ); $statement = $db->prepare( 'INSERT INTO table VALUES( :username, :password )' ); $statement->bindValue( ':username', $dirtyUsername ); $statement->bindValue( ':password', $dirtyPassword ); $result = $statement->execute(); // result checking ommited for brevity 

With PDOs and trained operators, there are more options. For example, you can easily reuse a prepared statement in a loop, as such:

 $statement = $db->prepare( 'INSERT INTO table VALUES( :username, :password )' ); foreach( $users as $dirtyUser ) { $statement->bindValue( ':username', $dirtyUser->username ); $statement->bindValue( ':password', $dirtyUser->password ); $result = $statement->execute(); // result checking ommited for brevity } 

Or pass placeholder bindings to the execution method, for example:

 $statement = $db->prepare( 'INSERT INTO table VALUES( :username, :password )' ); $result = $statement->execute( array( ':username' => $dirtyUsername, ':password' => $dirtyPassword ) ); // result checking ommited for brevity 

... etc. etc.

Answer 3

Do not worry about running away from yourself (you will ruin it). Use the database level where you first prepare the instruction, and then add data to it.

In PHP you have to use PDO . You write

 SELECT * FROM table WHERE key = :key AND value = :value 

and then add the data to the calling functions.

Answer 4

If the data is a string, it should always be escaped.

However, it is better to use the parameters.

Answer 5

When in doubt, avoid all this. May not be too safe.

Good good. I get it

ALWAYS ESCAPE

Answer 6

If you are generating SQL yourself, and not using something like PDO, you should always avoid strings.

String exclusion is a basic requirement of the SQL language. This is what allows characters, such as apostrophes or backslashes in a string, to be used without any degradation. There is no situation where strings are not required to be avoided.

Even non-strings must be filtered to ensure that they are not really strings.

If you are studying, think seriously about learning something like PDO, as many others have said, rather than avoiding your own lines.

Answer 7

You avoid the MySQL query string when any string consists of user input, for example:

in PHP: $ username =; // VALUE FROM USER INPUT

then your query string: "INSERT INTO table ('username') VALUES (". $ Username. ")"

You would have to avoid this mySQL query because the $ username variable could truly have malicious code inserted by the client to enter into your database.

Answer 8

When to run? As soon as your site becomes publicly available.