CODEWITHSUNDEEP
phpsql-serverpdo
Nico Gestiada
Nico Gestiada

Reputation: 21

Unable to alter SQL using PDO statement

if (isset($_GET['ResetPassword']))  { 
    $name = $_GET['name'];
    $sql = "ALTER LOGIN $name WITH PASSWORD=N'Nico1234!'";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':name', $_GET['name'], PDO::PARAM_STR); 
    $stmt->execute();
}

Hi guys I cant alter the password of a certain name(user) Where the name is from get (Selected from the sql). Thanks for the help.

Upvotes: 0

Views: 116

Answers (2)

Zhorov
Zhorov

Reputation: 29983

If you want to change the properties of a SQL Server login account, use ALTER LOGIN. The problem here will be the parameter in your statement. Table and column names cannot be replaced by parameters in PDO. I'm not sure, but I think that it's the same for login names. So, in this case, you should use statement without parameters, escape special characters and sanitize the data manually.

As a note, when you want to use a parameter, the correct syntax for a placeholder is :name or ?, not $name.

<?php
...
try {
   # SQL Authentication
   $conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
   # Windows Authentication
   #$conn = new PDO("sqlsrv:server=$server;Database=$database");
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
   die( "Error connecting to SQL Server".$e->getMessage());
}

...
try {
    $name = $_GET['name'];
    $password = 'Nico1234!';
    # Escape special characters and do some check for $name and $password values 
    $stmt = $conn->prepare("ALTER LOGIN $name WITH PASSWORD = N'$password'");
    $stmt->execute();
} catch( PDOException $e ) {
    die("Error executing query: ".$e->getMessage() );
}
...
?>

ALTER LOGIN needs permissions to execute correctly. If you use Windows authentication, then the Web server's process identity or thread identity (if the Web server is using impersonation) is used to connect to the SQL Server. Use next script for more information (change between SQL and Window authentication):

<?php
# Connection
$server    = 'server\instance,port';
$database  = 'database';
$uid       = 'uid';
$pwd       = 'pwd';

# PDO Connection
try {
    # SQL authentication
    #$conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    # Windows authentication
    $conn = new PDO("sqlsrv:server=$server;Database=$database");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die( "Error connecting to SQL Server".$e->getMessage());
}

# 
try {
    $stmt = $conn->query("SELECT 'SUSER_SNAME' AS [NAME], CONVERT(nvarchar(128), SUSER_SNAME()) AS [VALUE]");
    # Data
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC) ){
        echo $row['NAME'].": ".$row['VALUE']."</br>";
    }    
} catch( PDOException $e ) {
    die( "Error executing query".$e->getMessage() );
}

#
$stmt = null;
$conn = null;
?>

Upvotes: 0

Updater
Updater

Reputation: 459

ALTER TABLE Statements are used for changing the schema of a Table like adding a Column or FOREIGN KEYS. Are you trying to make an UPDATE Statement? The right query would be:

"UPDATE Login SET PASSWORD='Nico1234!' WHERE name=:name"

If you want to add the $_GET['name'] Parameter to the statement, you have to use :name anywhere inside it.

Upvotes: 1

Related Questions