1

suppose I have a LIKE sql statement stored in a variable like this:

$movie_title = $_POST['movie_title'];
$query= "SELECT movie FROM movies WHERE title LIKE '%" . $movie_title . "%'";

What instance of an sql injection attack would I prevent by escaping the wildcards % and _?

user701510
  • 5,563
  • 17
  • 61
  • 86
  • 2
    I think there is a special hell for people using thigs like `LIKE '%" . $movie_title . "%'"` and for people storing passwords as plain text – Milan Halada May 21 '12 at 07:27
  • 2
    possible duplicate of http://stackoverflow.com/questions/3683746/escaping-mysql-wild-cards/3683868 – kapa May 21 '12 at 07:28
  • @Uriel_SVK What are you talking about? I was using the syntax for visibility purposes. – user701510 May 21 '12 at 07:32
  • Well, use PDO and add wildcards to your variable and insert this variable into query using parameters. You dont have to care for escaping then. NEVER insert unsanitized user input into query. – Milan Halada May 21 '12 at 07:51
  • 1
    @user701510 parameterization protects you against malicious inputs. But to assure proper searching for literal % or _ in your movie title, you would want to escape any that appear in the user input so they are not treated as wildcards. – Andrew Barber May 21 '12 at 08:02

2 Answers2

2

Using PDO:

$pdo = new PDO(/* db info */);

$original = $_POST['movie_title'];
$wildcarded = '%'.$original.'%';
$stmt = $pdo->prepare('SELECT movie FROM movies WHERE title LIKE :var');
$stmt->bindParam(':var', $wildcarded);
$stmt->execute();
// fetching and stuff...
Milan Halada
  • 1,943
  • 18
  • 28
  • Is there a reason for escaping wildcards? What type of security hole do unescaped wildcards present? – user701510 May 21 '12 at 07:58
  • 2
    This could be sufficient to prevent malicious input, but would not help if % or_are entered literally in the movie title field; they would be treated as wildcards tothe Like clause. – Andrew Barber May 21 '12 at 08:00
  • I can not actually imagine any way how wildcards could be used to do something malicious – Milan Halada May 21 '12 at 08:10
  • 1
    @Uriel_SVK: It depends on what the SQL query is used for: and what does the application do. Imagine a DELETE query where the WHERE condition doesn't work the way the programmer intended, because the attacker includes SQL wildcards like "%" in the string — thus deleting many more records than the programmer intended to allow! Similarly, an attacker might SELECT/ UPDATE records they're not supposed to. – Matthew Slyman Apr 29 '16 at 14:24
  • Even a SELECT that normally uses an index can be destroyed by prefixing a `%`, causing an unintended table scan because the index can no longer be used. – Timo Jun 07 '16 at 15:27
0

Very simple:

Write a function like this:

function sqlwildcardesc($x){
    return str_replace(array("%", "_"), array("\\%", "\\_"), mysql_real_escape_string($x));
}

now your query:

$query= "SELECT movie FROM movies WHERE title LIKE '%".sqlwildcardesc($movie_title)."%'";

this should work! i tested it in my own project!! have fun ;)

silez
  • 9
  • 1
  • **BE CAREFUL**. `str_replace()` has no appreciation for the character set of your database connection. Performing a simple bytewise replacement as it does - especially on the output of `mysql_real_escape_string()` - could open the door to multibyte attacks. – eggyal Apr 27 '14 at 14:52
  • This is insecure code, do not use escape, parameterize. – Kzqai Jan 15 '20 at 16:27