Security concern for SQL Injections/XSS
Preventing SQL Injection


  A lot suggest using mysql_real_escape_string 


Some people do suggest this, but it's a bad suggestion. The only proper defense against SQL injection are prepared statements.

Escaping is at best a second-best solution in case you cannot use prepared statements for some reason.


  But that isn't an option, since it's depreciated now.


mysql_ is deprecated, and has been for quite some time. mysqli_ however is not, and it does offer mysqli_real_escape_string. Still, prepared statements are the way to go.

The good news is that you use prepared statements, and use them correctly, so you are defending against SQL injection exactly as you should be.

Your additional Filters

That being said, your additional filters are not helping, and are in fact adding a false sense of additional security:


Replacing  with _ doesn't really prevent anything. Pretty much all attacks I can imagine would likely still be possible, depending on the concrete situation. For SQL injection, you could eg use comments (/**/) instead of spaces.
your regex checks if there is any word character given. So ' and 1=1-- would pass through it, as a is a word character.


But as I said, your code is secure against SQL Injection, so you don't need additional protection. Still, input filtering as defense in depth is a good idea (just not the way you were filtering), although I would do it in a centralized place and a bit more structured. See eg here.

As you mention XSS: As you are not echoing anything variable, you do not need to worry about XSS at the moment.

Misc


your indentation is off, making your code hard to read (it's eg hard to see what the last else closes).
your spacing is also off, as is your positioning of brackets.
you never use $pname.
it's not clear to me why you sometimes die and sometimes echo.
you can make your queries more readable by separating keywords and variables by case. So for example, keywords could be all uppercase, variables all lowercase.


Given all of this, your code could look something like this:

require 'connection.php';
if(!isset($_GET['pname'])) {
    return "&lt;p&gt;Error&lt;/p&gt;";
}

$query="SELECT schema_name FROM information_schema.schemata WHERE schema_name = :pname;";
$query_params = array( 
    ':pname' =&gt; $_GET['pname']
);

$prep = $conn-&gt;prepare($query);
$result = $prep-&gt;execute($query_params) or die("failed!");
$row = $prep-&gt;fetch(); 

if($row) { 
    echo "Exists" ; 
} else {
    echo "doesn't" ;
}